topic Re: Master Calendar and Calculated Element List in Set Analysis in QlikView

Master Calendar and Calculated Element List in Set Analysis

dena_reavis — Thu, 09 Nov 2017 17:44:10 GMT

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

Re: Master Calendar and Calculated Element List in Set Analysis

dena_reavis — Mon, 13 Nov 2017 16:26:51 GMT

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!

Re: Master Calendar and Calculated Element List in Set Analysis

sunny_talwar — Mon, 13 Nov 2017 16:33:39 GMT

Try this

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

Re: Master Calendar and Calculated Element List in Set Analysis

dena_reavis — Mon, 13 Nov 2017 16:37:51 GMT

You are awesome!!! Thank you so much!

Re: Master Calendar and Calculated Element List in Set Analysis

dena_reavis — Mon, 13 Nov 2017 18:00:12 GMT

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)

Re: Master Calendar and Calculated Element List in Set Analysis

sunny_talwar — Mon, 13 Nov 2017 18:06:47 GMT

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?

Re: Master Calendar and Calculated Element List in Set Analysis

dena_reavis — Mon, 13 Nov 2017 19:31:29 GMT

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

Re: Master Calendar and Calculated Element List in Set Analysis

sunny_talwar — Mon, 13 Nov 2017 19:35:51 GMT

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?

Re: Master Calendar and Calculated Element List in Set Analysis

dena_reavis — Mon, 13 Nov 2017 20:41:56 GMT

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

Re: Master Calendar and Calculated Element List in Set Analysis

sunny_talwar — Tue, 14 Nov 2017 01:16:13 GMT

May be this for previous year (I guess)

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

Re: Master Calendar and Calculated Element List in Set Analysis

dena_reavis — Thu, 21 Dec 2017 16:56:49 GMT

Hi Sunny,

I was wondering, is there any way to get the August value to calculate using the Before function? It is the first value and I was hoping for a way to get it to calculate even though it would be the July value which falls in the previous fiscal year. Our fiscal year runs August through July.

Thank you,

Re: Master Calendar and Calculated Element List in Set Analysis

sunny_talwar — Thu, 21 Dec 2017 17:06:27 GMT

Created a new MonthYear field in the script and use this expression

Only({<YEAR_AGE = {0}>}Aggr(Above(Sum({$<Group={'1','2','3'}>} Amount), 1), MonthYear))