To calculate MTD, YTD and LTD in Load Script

[cramkumar86]

I have a requirement to calculate MTD, YTD and LTD within Qlik and I would like to do that in load script itself. I do not want to use set analysis. Could you please help?

In the attachment, you will find 3 columns in yellow and they are updated with formulas. I would like to calculate the same in Qlik as well.

As you could see in the attachment, MTD and YTD and LTD should be calculated based on the report date I select on the filter.

Example: When I select 3/15/2023,

MTD should be sum(transactional_amt) from 3/1/2023 to 3/15/2023

YTD should be sum(transactional_amt) from 1/1/2023 to 3/15/2023

LTD should be sum(transactional_amt) from MIN(REPORT_DATE) to 3/15/2023

[vinieme12]

MTD

sum( {<  REPORT_DATE = {">=$(=Date(MonthStart(Max(REPORT_DATE))))<=$(=Date(Max(REPORT_DATE)))"} >} transactional_amt)

YTD

sum( {<  REPORT_DATE = {">=$(=Date(YearStart(Max(REPORT_DATE))))<=$(=Date(Max(REPORT_DATE)))"} >} transactional_amt)

LTD

sum( {<  REPORT_DATE = {">=$(=Date(Min(REPORT_DATE)))<=$(=Date(Max(REPORT_DATE)))"} >} transactional_amt)

[cramkumar86]

Thanks for the response. But as mentioned, I would prefer to calculate these on the load script. I guess set analysis cannot be used in the load script.

[vinieme12]

---

@cramkumar86 wrote:

Example: When I select 3/15/2023,

---

if you ought to be making selections on Report_Date then you absolutely cannot pre-calculate in load script

[cramkumar86]

Thank you. Just FYI on why I cannot use set analysis.

Due to high data volume, I am using ODAG to limit the data corresponding to user's selections on the selection App. When users select the report date and if thats included in ODAG expressions, the template app will fetch the data only for the date that has been selected. The disadvantage with this approach is, due to the limited data it loads, all the MTD, LTD and YTD numbers are going for a toss when I use set analysis. Excluding the REPORT_DATE column from ODAG expression will help. But thats going to cause performance issues. Thats why wanted to check if there is any option to handle this in Qlik. Precalculating these numbers on the DB layer is one option I am thinking of.

[vinieme12]

i would suggest creating flags instead

https://community.qlik.com/t5/Member-Articles/Period-Presets-Compare-Periods-on-the-fly/ta-p/1486371https://community.qlik.com/t5/Member-Articles/Period-Presets-Compare-Periods-on-the-fly/ta-p/1486371