Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I'm just dealing with a concept of monthly quotas in my Qv application. I found a nice way to code-one-use-many by creating a variable with $1 where I have to place the quota, it works properly when the scope is monthly, and also in daily. The problem comes when I get yearly overview, it does it correctly per month basis but the year sum shows a bit more than the quota, is not a blocking problem because the scope is monthly based and when I select the month it gets me the correct sum, but before I get in production I'll like to hear some experiences related to that topic, as I might introducing a potential error I'm not aware yet
I attach the qvw with the formula used for the monthly quota.
My question, is it correct the approach I use for calculating a monthly quota that can be also applicable to a daily basis? If not, what would be the best approach for such cases?
Thanks
Julio
Hi Sunny,
Thanks for your update, I actually manage to fix it by the end of my work day. My plan was publishing the answer, so here it goes!
So my mistake was that I used Count(Day) instead of Count(dDay).
Correct formula:
Count(Distinct dDate)*$1*(1 + Year(Max(dDate)) - Year(Min(dDate)) + Month(Max(dDate)) - Month(Min(dDate)))/Ceil(Monthend(Max(dDate)) - Monthstart(Min(dDate)))
My granularity is in day, the quota is based on monthly view, so what I do, I calculate the quota for one day
$1*(1
+ Year(Max(dDate)) - Year(Min(dDate)) + Month(Max(dDate)) -
Month(Min(dDate)))/Ceil(Monthend(Max(dDate)) - Monthstart(Min(dDate)))
and then I multiply for the amount of days the dimension has
Count(Distinct dDate)
and there was my mistake, I didn't count the days in the dimension, I calculated the days in the month/months that it can be 28 or 29 or 30 or 31, when actually I need the sum of all days in each month.
Now for a full year with a monthly quota of 3000 you get 36000
Thanks!
Julio !
Can you check now, if this looks better
Hi Sunny,
Thanks for your update, I actually manage to fix it by the end of my work day. My plan was publishing the answer, so here it goes!
So my mistake was that I used Count(Day) instead of Count(dDay).
Correct formula:
Count(Distinct dDate)*$1*(1 + Year(Max(dDate)) - Year(Min(dDate)) + Month(Max(dDate)) - Month(Min(dDate)))/Ceil(Monthend(Max(dDate)) - Monthstart(Min(dDate)))
My granularity is in day, the quota is based on monthly view, so what I do, I calculate the quota for one day
$1*(1
+ Year(Max(dDate)) - Year(Min(dDate)) + Month(Max(dDate)) -
Month(Min(dDate)))/Ceil(Monthend(Max(dDate)) - Monthstart(Min(dDate)))
and then I multiply for the amount of days the dimension has
Count(Distinct dDate)
and there was my mistake, I didn't count the days in the dimension, I calculated the days in the month/months that it can be 28 or 29 or 30 or 31, when actually I need the sum of all days in each month.
Now for a full year with a monthly quota of 3000 you get 36000
Thanks!
Julio !