filter by formula in pivot

Answered

Comments

1 comment

  • Avatar
    Michael Becker

    Hi Sivan,

    if you have a table which defines all branches, and then log table with logs,you could create a couple additional custom Tables in ElastiCube with following syntax in following order:

    Table 1 :

    SELECT year(logs.date) as year, month(logs.date) as month FROM logs

    GROUP BY year(logs.date), month(logs.date) 

    Table 2:

    SELECT branch.id, branch.name, [table 1.year], [table 1].month FROM branch

    CROSS JOIN [table 1]

    Table 3:

    SELECT logs.[branch id], month(logs.date) as Month, year(logs.date) as Year, COUNT(logs.id) as logins FROM logs

    GROUP BY logs.[branch id], month(logs.date), year(logs.date)

    Table 4:

    SELECT [table 2].id, [table 2].year, [table 2].month, [table 2].name, [table 3].logins FROM [table 2] as a

    LEFT JOIN [table 3] as b

    ON a.id = b.[branch id] AND a.year = b.year AND a.month = b.month

     

    That should give you table with all possible months, years and branches combination + count of logs. If there will be no logs for given combination it will be NULL. Now you will have to play around on the Pivot how to display that(possibly create an additional column in table 4 query which will be 0/1 value depending on the count for given combination). Hope it helps

    Regards,

    Michał

     

     

    1
    Comment actions Permalink

Please sign in to leave a comment.