Qlik Community

QlikView Documents

Documents for QlikView related information.

Calculation of MTD, YTD, QTD, WTD in script level - The As-Of table concept - MTD, YTD, QTD, WTD calculation made easy

ananyaghosh
Contributor III

Calculation of MTD, YTD, QTD, WTD in script level - The As-Of table concept - MTD, YTD, QTD, WTD calculation made easy

Hi,

I have prepared a script that will calculate the MTD, YTD, QTD, WTD without using complex set analysis using the As-Of table concept.

Also from my script various types of calculations can be made like current day sale, previous day sale, current month sales, previous month sale , rolling period of N no of months without a complex set analysis and with converting a date into numbers.

This is the ultimate solutions above all the posts that is related to MTD, YTD, QTD, WTD who is using complex set analysis.

I have attached the sample data source and also the sample QVW file for reference and last but not the least I am very thankful to Qlik Community and their users from which I got help to prepare this script and QVW document.

So use it and if face any problem please let me know.

Use AsOfDate column as dimension and use the following expression for different purposes:

Current Day Sale: sum({<FlagDate={'Current Day'}>}[Sales])
Previous Day Sale: sum({<FlagDate={'Previous Day'}>}[Sales])

Current Year Sale: sum({<FlagYear={'Current Year'}>}[Sales])
Previous Year Sale: sum({<FlagYear={'Previous Year'}>}[Sales])

Current Month Sale: sum({<FlagMonth={'Current Month'}>}[Sales])
Previous Month Sale: sum({<FlagMonth={'Previous Month'}>}[Sales])

Current Week Sales: sum({<FlagWeek={'Current Week'}>}[Sales])
Current Week Sales: sum({<FlagWeek={'Previous Week'}>}[Sales])


MTD Sale: sum({<FlagMTD={1}>}[Sales])
WTD Sale: sum({<FlagWTD ={1}>}[Sales])
YTD Sale: sum({<FlagYTD ={1}>}[Sales])
QTD Sale: sum({<FlagQtd ={1}>}[Sales])

Thanks,

Sandip

Attachments
Comments
satheshreddy
Contributor III

Hi ananyaghosh,

could you please attache the total script in one word format, it would be help full for beginners also .

Regards

Sathish

ananyaghosh
Contributor III

I will do that.

Not applicable

Great work

bennywoo
New Contributor II

It is very useful to my application.

ananyaghosh
Contributor III

Thanks

ecolomer
Honored Contributor II

Thank's for sharing.

Good Work.

Saludos,

Enrique Colomer

msteedle
Contributor

I think this is great when the application needs do not call for dynamic point in time analysis.

One opportunity to improve the calculation performance and perhaps make it a little more flexible in the UI would be to switch from text descriptions to numeric values.

An intuitive way to have numeric values represent what you have in text would be treating the current period as 0, the prior period as -1, the period before that as -2, etc. That way you could use the field to compare to the prior period and define ranges in your set, like the last x periods, in addition to doing numeric rather than text comparisons.

Ex.

Current Day Sale: sum({<FlagDate={0}>} [Sales])
Previous Day Sale: sum({<FlagDate={-1}>} [Sales])

Current Year Sale: sum({<FlagYear={0}>} [Sales])
Previous Year Sale: sum({<FlagYear={-1}>} [Sales])

Not applicable

Hi, Does anyone have experience using this with Qlik Sense? I added the script but the results were not as expected.

Thank you,

ananyaghosh
Contributor III

Hi,

I have used it in Qliksense too. So what kind of problem are u facing now?

Thanks,

Sandip

Version history
Revision #:
1 of 1
Last update:
‎05-07-2017 12:34 PM
Updated by: