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.
