topic Re: Calculate MTD from YTD values in QlikView

Calculate MTD from YTD values

priyanka08 — Thu, 12 May 2022 04:59:03 GMT

I have all values in YTD for every month ,i.e . Jan YTD 2022 include data from 01/01/2022 to 31/01/2022

feb YTD include data from 01/01/2022 to 28/02/2022 and mar include data from 01/01/2022 to 31/03/2022

but need to represent this Line chart MTD data where month -year i.e. Jan-21 is x axis

In order to represent in textbox ,I subtract current month YTD to last month YTD for example for

Feb MTD= YTD feb- YTD Jan

i.e. 16-10= 6

I am facing difficulty to implement the same in line chart

Re: Calculate MTD from YTD values

ZouhairLahlou — Thu, 19 May 2022 14:58:13 GMT

Hi Priyanka08,

I would prepare my data in the script in order to have it ready to be plotted. To substract current month to the previous one, i would order my table by Month Asc then use PREVIOUS(). Here's a sample :

YTD : 
Load * Inline 
[ MONTH, YTD
1, 100
2, 150
3, 340
4, 500
5, 600
6, 750
7, 965
8, 1000
9, 1145
10, 1647
11, 1800
12, 2000];




MTD : 
NoConcatenate 
Load *, 
if(isnull(Previous(YTD)), YTD,YTD - Previous(YTD)) as MTD 
Resident YTD;

DROP Table YTD;

Regards,

Zouhair