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: 
Not applicable

How to get previous month data in 1 of the column of straight table

Guys,

I have 1 requirement in which i have below mentioned columns:

1) month (MMM/YYYY)

2) Average salary

3) previous month average salary

i need to use dynamic function(like set analysis) to achieve previous month average, as we have some filters as well like month selection, Area, year etc.

Kindly suggest how can we achieve this.(i have tried the set analysis and above/below functions but above functions wont work here as we can select any months then it would give wrong informations)

5 Replies
sunny_talwar

If month is your dimension, I think the only way you would be able to achieve this can be achieved using Above() function. Unless you have a an example to look at, it would be difficult to give you a more precise answer, but you can search on the community for how the Above() function can help.

Not applicable
Author

hey thanks Sunny!

but here i cant use the Above()/below() functions. Because as i mentioned in my scenario i  have list box on months so i can select any month.So in that situation it wont give me proper previous month average salary.

Is there any way to achieve this from set analysis because i am looking for any dynamic solution.

sunny_talwar

You can still get correct results with selection in month. Set analysis is evaluated once a chart, so I don't think set analysis will be helpful here. If you can share a sample, we might be able to help you get the expected output.

Not applicable
Author

No Sunny,

If I ll select month like Feb2015, may2015 and sep2015. then 3 rows would come.Like below:

Month     current month avg sal     prev month avg sal

Feb               Feb avg sal                    Jan Avg sal

May               May avg Sal                    Apr avg sal

but below/above function would give me next/before avg value. that's not correct.

I believe, now u would have correct idea of requirement.

sunny_talwar

Check this out:

Capture.PNG

Expression for Previous Month: =If(Sum(Salary) > 0, Above(Avg({<MonthYear>}Salary)))