topic Re: How to consider running three months where numerator should be current month and denominator should pick the 3 months previous values in a bar chart? in QlikView

How to consider running three months where numerator should be current month and denominator should pick the 3 months previous values in a bar chart?

Anonymous — Thu, 05 Jul 2018 07:54:51 GMT

Hi,

I need to create a bar chart where I need to show the running 3 months as my dimension and in the expression I should consider below logic:

= Sum ( A)

Sum ( {<Monthname = {"$(=MonthName(AddMonths(Monthname,-3)))"} } B)

-> =if(Num(Monthname) >= '$(=vRunning3Mon)' and Num(Monthname) < '$(=vRunning3Mon_Max)' ,Monthname) this is my expression for the Dimension which gives the previous 3 months bars.

-> When I use Monthname = {"$(=MonthName(AddMonths(Monthname,-3)))"} in my denominator my expression does not get evaluated.

I have also attached a screenshot of desired output

- 1st chart is without any logic i.e., numerator and denominator considers same month. this can be achieved.

- 2nd Chart is with the logic, Numerator will have month from the dimension and my denominator should consider the value 3 months previous to it. Facing issue with this requirement.

Can anyone please help me out here?

Thanks in advance.

Re: How to consider running three months where numerator should be current month and denominator should pick the 3 months previous values in a bar chart?

ogautier62 — Thu, 05 Jul 2018 08:49:02 GMT

Hi,

you have MonthName in dimension,

then the expression sum on MonthName - 3 so you find 0

Add sum(total ....

in your expression

regards

Re: How to consider running three months where numerator should be current month and denominator should pick the 3 months previous values in a bar chart?

Anonymous — Thu, 05 Jul 2018 10:07:26 GMT

Hi Olivier,

Thanks for your reply.

Add sum(total ....

in your expression

even this doesn't work.

Regards

Re: How to consider running three months where numerator should be current month and denominator should pick the 3 months previous values in a bar chart?

sunny_talwar — Sun, 08 Jul 2018 21:02:06 GMT

May be this

Sum(A)/Above(Sum(B), 3)

Re: How to consider running three months where numerator should be current month and denominator should pick the 3 months previous values in a bar chart?

Anonymous — Mon, 09 Jul 2018 14:24:24 GMT

Hi Sunny,

This will not give me the desired output since am already restricting the dimension for only 3 months when we give Above( Sum(B), 3) this will not find any value which is 3 rows above of it.

Re: How to consider running three months where numerator should be current month and denominator should pick the 3 months previous values in a bar chart?

sunny_talwar — Mon, 09 Jul 2018 14:27:51 GMT

Why not? I am not sure what you mean when you say you are restricting the dimension for only 3 months? You are using calculated dimension? If you are... then i suggest not to use that and instead use set analysis

Re: How to consider running three months where numerator should be current month and denominator should pick the 3 months previous values in a bar chart?

Anonymous — Wed, 11 Jul 2018 05:39:51 GMT

Yes am using a calculated dimension to get previous 3 months.

I tried this on set analysis, but am having 2 conditions on month itself so I used a calculated dimension and in my set analysis I want to incorporate the logic where for a month the denominator should pick a value which is 3 months back and numerator will have the same month.

ex : if the bar is in may then my exprn should look like

: = Sum( <May> A) / Sum( <Feb> B)

I need help in achieving this.

Re: How to consider running three months where numerator should be current month and denominator should pick the 3 months previous values in a bar chart?

sunny_talwar — Wed, 11 Jul 2018 10:35:38 GMT

Would you be able to provide a sample or sample data in an Excel format and based on the sample provided... give us the exact output you are hoping to get?