Hi folks, I have a requirement to show the average of a fact field for all 12 months in a particular year. However, the package structure only returns rows where they exist.
How can I generate a list of YearMonth (based off of a date field) so I can ensure that each UserId is returned 12 times, ensuring the proper denominator for the calculation?
Current output:
YearMonth UserId Count
201603 1234 12
201611 1234 4 >>> Average=8
Desired output:
YearMonth UserId Count
201601 1234 0
201602 1234 0
201603 1234 12
201604 1234 0
201605 1234 0
201606 1234 0
201607 1234 0
201608 1234 0
201609 1234 0
201610 1234 0
201611 1234 4
201612 1234 0 >>> Average=1.33
Thanks!
Looks like you might need a calendar table and a package that allows cross product joins.
I don't have access to Framework Manager - is there a way to do this using the functionality of Report Studio alone? Thanks
You can do this with a SQL object:
with dates as (
select #prompt('StartDate','date','current_date')# as DT from (values('all_level')) "Query1.0"(c1)
union all
select _add_days(DT, 1) as DT from dates where DT < #prompt('EndDate','date','current_date')# )
select DT from dates
Interesting - I have not worked with SQL objects yet but I will give this a shot! Thanks for your help folks
The SQL object will give you a single date field. In the query subject you can create your yearmonth data item.
Then join that with your fact table and make sure to have an outer join on the fact table side of the join (0..n).