Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
SAY MY expression IS sum(sALES)
i have a week cycle week ( so i need to divide by number of days in that week )
I have a half year cycle , so i need to divide by number of days in that year-half
Year cycle , i need to divide by number of days in year
quarter cycle .. so i need to divide by number of days in that quarter
each is a separate expression for each cycle using a condition
The latest period in year for eg has a few days as its not year end , that is the complication so i cant just say 365
It seems to me you need to create a master calendar with flags (0/1) for the various dates.
E.g
isinQTD
isinYTD
isinMTD
isinCurrweek
these flags should exist down to the day level so each day has a flag for every time period. then you can use a set analysis expression for each flag, and aggregate using avg.
Below is a link to a query I ran on the Design Blog that might be of help along with what the other posted provided:
Hopefully one or more of the posts may help you get to where you need to be here.
Regards,
Brett