topic Monthly quota and parametrize variable in QlikView

Monthly quota and parametrize variable

Thu, 04 Aug 2016 08:16:25 GMT

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

Re: Monthly quota and parametrize variable

sunny_talwar — Thu, 04 Aug 2016 16:26:02 GMT

Can you check now, if this looks better

Re: Monthly quota and parametrize variable

Fri, 05 Aug 2016 06:30:14 GMT

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 !