Master Calendar and Calculated Element List in Set Analysis

[dena_reavis]

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:

I am trying to show

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!!

[dena_reavis]

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 · Accepted solution]

Try this

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

[dena_reavis]

You are awesome!!! Thank you so much!

[dena_reavis]

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]

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]

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)