Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More

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

0 Likes
bennywoo
New Contributor II

It is very useful to my application.

0 Likes
ananyaghosh
Contributor III

Thanks

0 Likes
ecolomer
Honored Contributor II

Thank's for sharing.

Good Work.

Saludos,

Enrique Colomer

0 Likes
Partner
Partner

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])

0 Likes
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,

0 Likes
ananyaghosh
Contributor III

Hi,

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

Thanks,

Sandip

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