Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

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.

bar.PNG

7 Replies
ogautier62
Specialist II
Specialist II

Hi,

you have MonthName in dimension,

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

Add sum(total ....

in your expression

regards

Anonymous
Not applicable
Author

Hi Olivier,

Thanks for your reply.

Add sum(total ....

in your expression


even this doesn't work.

Regards

sunny_talwar

May be this

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

Anonymous
Not applicable
Author

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.

sunny_talwar

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

Anonymous
Not applicable
Author

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.

sunny_talwar

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?