- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To calculate MTD, YTD and LTD in Load Script
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
If a post helps to resolve your issue, please accept it as a Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
If a post helps to resolve your issue, please accept it as a Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If a post helps to resolve your issue, please accept it as a Solution.