Convert KPI from MTD to YTD

[asinghal0412]

Dear Qlik Community,

The Dimension is called NPV ,  Net Present Value and is calculated for different currency rate as in the code below.

Now this KPI is MTD and I want to convert it into YTD. How should I edit the code to achieve this result?

=if (X_rate='X_JPY', Money ([NPV [LC]]]*($(X_rate))/1000000,'#,##0.0 M¥', '.' , ',' ),
if(X_rate='X_EUR', Money ([NPV [LC]]]*($(X_rate))/1000,'#,##0 k€', '.' , ',' ),
if(X_rate='X_USD', money([NPV [LC]]]*($(X_rate))/1000,'#,##0 k$', '.' , ',' )
)))

If you need any other clarification please let me know!

Thanks & Best,

AS

[Dalton_Ruer · Accepted solution]

This doesn't even make sense to me because you aren't aggregating anything at all in your expressions. Meaning it will simply calculate the expression for each and every row of your data individually.

I typically use a Master Calendar (search for that) with flags for MTD, PMTD, YTD, PYTD, FiscalYear etc and then simply do math based on the flag. Meaning if the date in the Master Calendar is within the MTD the flag would be 1, if not it would be 0. 

So MTD_FLAG * FieldValue   would result in the value of the field if it is in the current month, or else it would be 0 if it is not. 

[asinghal0412]

Hi Dalton,

Thanks for your response. I have pasted below another link where I have another similiar question with a better example. Can you look at that?

https://community.qlik.com/t5/New-to-Qlik-Sense/YTD/m-p/1805107#M184436

Thanks,

AS

[Dalton_Ruer · Accepted solution]

My suggestion is that you begin thinking about these things (both examples) in your load script. Here is a simple data set and the code for the example for Year to Date and Previous Year to Date

Data:
Load * Inline [
Field, PostingDate, Value
A, '01/01/2020', 100
A, '05/02/2021', 200
B, '01/03/2021', 50
B, '12/19/2020', 60
C, '03/21/2021', 75
C, '05/19/2020', 70
];

// This self join could just be done as preceding load but not many are comfortable with that so I break it apart logically
JOIN Load
// Load the fields that make it unique
Field, PostingDate,
IF (InYearToDate(PostingDate, Today(), 0), Dual('Yes', 1), Dual('No', 0)) as YTD_Flag,
IF (InYearToDate(PostingDate, Today(), -1), Dual('Yes', 1), Dual('No', 0)) as PYTD_Flag
Resident Data;

Once you have created these very simple flags then your calculations can be super easy. In this screen shot I show a table with the raw data, and then I do the calculations. Notice that I used DUAL so that I can see human friendly Yes/No flag that tells me the value is in YTD or in PYTD. But since it also has 1 I can simply multiply my values by the flags. If it is YTD then it's 1 X Value. If it's not YTD then it's 0 x Value so it results in a total for only values that are in YTD or are in PYTD. 

The InYearToDate function also handles a PERIOD start field so if you want to deal with Fiscal or rolling years you can pass a value like 4 if you want to start in April, or 10 if you want to start in October etc. 

There is also an InMonthToDate function that does the same. 

As you start expanding these really nice to have flags you eventually get to what is called a Master Calendar. Where you simply build a table with every single date and all of the possible flags you want. Holidays, Weekends, Week #', YYYY-MM, Quarters etc. And you will have them handy in all of your applications. 

[asinghal0412]

Hi Dalton,

Thank you for your support and detailed explanation. Much appreciated.

Best,

Akash Singhal