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: 
dena_reavis
Employee
Employee

Master Calendar and Calculated Element List in Set Analysis

Dear Experts,

I am trying to create a set analysis to calculate the previous month's total, so it can be compared with totals in the month, and calculate other things like change from previous month, etc. But I can't seem to get my calculation right. My master calendar has a field for the age of the month, where the current month is 0 and last month is 1, so on. I am trying to get the month age to increment in the element list, but I do not have this right.

The attached file has some sample data and my calendar. this is what I have:

have.png

I am trying to show

want.png

I have made many tries and all I seem to get is the same month:

sum({$<MONTH_AGE ={"=$(=max(MONTH_AGE)+1)"} >} Amount)

At first I tried it without the max(MONTH_AGE) because I didn't think I needed it. I'm not sure what it is wrong with my syntax. Please help.


Thank you!!

1 Solution

Accepted Solutions
sunny_talwar

Try this

Before(Sum({$<Group={'1','2','3'}>} Amount), 1)


Capture.PNG

View solution in original post

11 Replies
dena_reavis
Employee
Employee
Author

HI,

Maybe I need to clarify this. In each month column, I just want to show the previous month's total. So in the September column, my first expression would be the total of August.

Anybody out there have a suggestion?

thank you!

sunny_talwar

Try this

Before(Sum({$<Group={'1','2','3'}>} Amount), 1)


Capture.PNG

dena_reavis
Employee
Employee
Author

You are awesome!!! Thank you so much!

dena_reavis
Employee
Employee
Author

Hi Sunny, this does work, but in my real data I may need to refer to months back or a prior year. Can you suggest a way to use the month age or the year age?

sum({$<MONTH_AGE ={"=$(=max(MONTH_AGE)+1)"} >} Amount)

sunny_talwar

From what I understand set analysis won't work... you will have to use Inter-record functions ‒ QlikView what is the issue you are having? Would you be able to share a sample?

dena_reavis
Employee
Employee
Author

Hi Sunny,

I expanded my sample data to include more dates. I am trying to present totals for the current month and the previous month, which the example you gave me work perfectly, but how do I show the total for the previous fiscal year. This is supposed to be comparisons of the different points in time.

Should I rework my calendar to make this easier?

Is this difficult because I am using the Month Name as the dimension? I admit, I thought this sounded simpler than it is.

Thank you

sunny_talwar

Are you planning to make a selection on the year field when you see this? I mean, right now when I opened your dashboard, nothing was selected. So, previous year value based on which year?

dena_reavis
Employee
Employee
Author

Sorry about that. I intended for the new chart to have the Year Age =0, which means that is the current year, so that no year selection had to be made. I am attaching the revised QVW file. What I am trying to get to is several different 'point in time' totals for each month, to show the previous month, the current month, that month in the prior year, etc.

Again, if you have a suggestion for how to rework the calendar to make this easier, please do. Anything to make this easier!

Thank you

sunny_talwar

May be this for previous year (I guess)

Sum({$<Group={'1','2','3'}, YEAR_AGE = {'-1'}>} Amount)