I have an app that currently uses ODAG approach to create on-demand reports. I have a DATE filter on my selection app. Selecting a date would mean that only the data corresponding to the selected date would load/appear on the on-demand report. As I am calculating MTD, LTD and YTD values within Qlik, loading only the dates selected on the selection app is causing issues with the above mentioned calculations. Due to this, I am ignoring the DATE filter so that I load data for all the dates and the calculations would work just as expected. However, I am afraid that it might cause performance issues in the long run. Hence, Is there an option to pre-calculate YTD, MTD and LTD in Qlik through data load script so that I dont have to load data for all the dates on the on-demand app?
Here is the formula I use for MTD in Qlik master item.
I am trying to modify the above formula to make it work on the data load scripting. I tried the below formula. But it does not seem to be considering the selections I make on the DATE filter. It always populates the SUM of data for all dates.
None of this makes sense in the context of a load script - you can't use set analysis in loads.
If you want to pre-calculate, you would need something along the lines of:
Load Dim1, Dim2, Sum(transactional_amt) as MTD_Amount
From SomeSourceTable
Where Date >= MonthStart($(od_YOURODAGFIELD)) and Date <= $(od_YOURODAGFIELD);
You could also place the condition into an if() statement, which would allow you to use multiple statements to get YTD, MTD, etc in a single Load statement.
However, this still requires loading all of the data because you won't be able to calculate the YTD values without YTD data...
Depending on how exactly your ODAG work, you may be able to use the original app or an additional app to pre-calculate all of the possible values for each available date, place them in a QVD, and then read only the appropriate value with the ODAG target app. This is only likely to be feasible if you have a limited number of options, though.
Thanks for the response. IF or WHERE conditions might not help as I would like the calculations to be done for each row.
If you could refer the attachment, I have updated MTD, YTD and LTD columns with formulas. I am excepting the same in Qlik as well. I would like Qlik to perform row level aggregations to find MTD, YTD and LTD.
Since I use ODAG, the template app will have all the data loaded into QVD. However, the on-demand app that gets generated out of ODAG should have limited data (based on user's selection) and the calculations mentioned above should not get impacted due to non-availability of all data.
This does not really help, unfortunately. To calculate row-level YTD, you need the underlying YTD rows in the data (either during load or on the front end). I can't think of a way around it except to pre-calculate if that's feasible. In the Excel you included, it should be simple to include all of the information in the app or calculate it in the load script, since there's only one dimension and you said you are passing a single data value to the app...
If you already have YTD/MTD in the selection app, I'm not sure why you'd need ODAG in the first place... but if that's the case, and you have access to Automation, you could probably set up something to write the appropriate values into a QVD and then have the target app read from that file?
