topic Re: Get previous month value with Set analysis in QlikView

Get previous month value with Set analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

After spending hours on this I decided to seek your help as I cannot find a solution, and I am sure it is quite simple, but I am a beginner in QV.

I have Excel source files where data is reported in YTD cumulated.

I would like to present it for each month in both cumulated and monthly figures, therefore I need to “de-cumulate” data.

The calculation is quite simple :

Current month Monthly figure = Current month Cumulated YTD – Previous Month Cumulated YTD, except for January where Current Month Monthly figure = Current month Cumulated YTD

This is applicable only if there is a value in current month, otherwise it should return 0 for monthly figure.

I have searched in numerous forum posts, found various solution which I have tired, using variables, set analysis, Above() function…without success.

In my data model, indicators names are entered as values under the INDICATOR_ID field, and I am using set analysis to present data in charts and graphs.

I am sure it is possible with the set analysis to get the previous period and then perform a difference between current month and previous month but I cannot find how for the given INDICATOR_ID.

Another option would be to use the load script to de-cumulate data and transform cumulated into monthly figures, but I would prefer to avoid this and use data which was found in the source file directly in QV.

You will find attached the QV file, the XLS source file and the table I would like to get as a result.

Your help will be greatly appreciated !

Re: Get previous month value with Set analysis

bhaskar_sm — Sat, 05 May 2018 17:08:43 GMT

Hi,

Please find attached the Qvw. that full fill your requirement through back end (scripting)

frankly speaking,i really don`t have idea how we can archive this in front end using set Analysis.

if you found this helpful, please mark this as helpful or correct

Regards,

Bhasker Kumar

Re: Get previous month value with Set analysis

Anonymous — Sat, 05 May 2018 19:43:05 GMT

Hi,

Thanks for you super quick answer. It seems to work, however I am not sure to fully understand how you did this in your script, could you explain your script in more detail ?

Also I would much prefer to do this using Set Analysis and not manipulate the data in the script, because then I lose the ability to use Indicator_ID field.

Anyone else has an idea on how to do this using Ste Analysis ?

Re: Get previous month value with Set analysis

mikaelsc — Sat, 05 May 2018 20:07:45 GMT

try creating a master calendar in your script: Creating A Master Calendar

(please use this as a base example, but dont use these fields:

YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

it should however contain all useful calendar fields, plus: add a sequential numeric value (MonthSerial) to each of the sequential unique months (careful: year-month) in order to be able to create set analysis expressions like max(MonthSerial) to get the latest month; max(MonthSerial-1) to get the previous month, max(Monthserial - 12) to get last year same month,...

(*also accumulations become possible (>=max(MonthSerial-12)) to get latest 12 months, etc etc etc)

if you're confident enough with Qlik Scripting take a look at @Rob Wunderlich's QV Components: https://github.com/RobWunderlich/Qlikview-Components/blob/master/QVC_Source/Qvc_Calendar.qvs

it contains examples for these sequential fields:

AutoNumber(MonthStart(Date),'$(_f_MonthSerial)') as $(_f_MonthSerial),

AutoNumber(QuarterStart(Date),'$(_f_QuarterSerial)') as $(_f_QuarterSerial),

AutoNumber(weekyear(Date) &'|' & week(Date),'$(_f_WeekSerial)') as $(_f_WeekSerial),

good luck

Re: Get previous month value with Set analysis

Anonymous — Mon, 07 May 2018 06:29:44 GMT

Thanks for the reply. I am not sure to see how creating a Master calendar can help but I will try

Re: Get previous month value with Set analysis

Anonymous — Mon, 07 May 2018 09:44:18 GMT

Hi,

I have built a Master Calendar in my script to follow your advice. I have tried to set up my new chart using the fields created in the Master Calendar, but it still does not work. See attached what I have done.

I am struggling with this for several hours now, without success.

What I would like to see is the table below, it looks quite simple but I cannot get it right.

	Cumulated YTD				Monthly
Month	Jan	Feb	Mar	Apr	Jan	Feb	Mar	Apr
Site
Site_A	0	8	16	21	0	8	8	5
Site_B	3	5	6	-	3	2	1	-
Site_C	0	7	15	-	0	7	8	-
Site_D	4	4	13	-	4	0	9	-
Site_E	1	4	5	-	1	3	1	-
Site_F	1	3	6	10	1	2	3	4
Site_G	1	1	3	-	1	0	2	-
Site_H	1	2	8	-	1	1	6	-

Any help would be much appreciated.

Re: Get previous month value with Set analysis

mikaelsc — Mon, 07 May 2018 19:49:25 GMT

sorry, didn't read the requirement correctly.

as Bhasker noted, what you want to achieve is not possible (?) using straightforward set analysis.

as you want to get monthly values from accumulated ones (IMHO, a nightmare), you need to script this...

in attachment a similar approach as Bhasker:

1. load table with your data but not in its final form,

2. create a calendar from the 1st date to the last available in your data

3. create an entry for all possible combinations of Site, Indicator, YearMonth

4. Add Data field or 0 for all combinations where they exist

4bis: sort records for interrecord caluclations

5. perform the calculations (and replace with null() where necessary

Re: Get previous month value with Set analysis

mikaelsc — Mon, 07 May 2018 19:51:33 GMT

Re: Get previous month value with Set analysis

mikaelsc — Mon, 07 May 2018 19:52:51 GMT

and the app

Re: Get previous month value with Set analysis

Anonymous — Tue, 08 May 2018 03:42:29 GMT

Thanks. I thought that it was simple but apparently it is not simple !

However I cannot open your file, I am currently using Qlikview 9 and it seems that this is using a more recent version of QV ?

Can you share a file that I can open with QV9 ?

Re: Get previous month value with Set analysis

mikaelsc — Tue, 08 May 2018 14:03:16 GMT

use qlik sense 🙂

Re: Get previous month value with Set analysis

Anonymous — Wed, 09 May 2018 14:21:58 GMT

Not really the coicie, QV is the tool used at my company, not Qlik Sense...

Thanks anyway for your help, I was able to solve my problem using your guidelines.