Qlik - 066: Get cumulative and rolling total/average using As of Table (As of Calendar)

In this video, we will create an As of Table (As of Calendar). Using this table, we will get rolling 3 months total, rolling 6 months total, rolling 12 months total, rolling 6 months average, cumulative total by year, non-cumulative totals.
You can directly jump to relevant sections from below timeline.
1) Introduction (00:00:01 - 00:00:55)
2) What is As of Table? (00:00:56 - 00:01:55)
3) Rolling 3 Months Total, rolling 6 Months Total and Rolling 12 Months Total (00:01:56 - 00:17:00)
4) Rolling 6 Months average (00:17:01 - 00:18:40)
5) Cumulative total by Year (00:18:41 - 00:22:22)
6) Non-cumulative total using as of table. (00:22:23 - 00:24:32)
Udemy Set Analysis Course
www.udemy.com/...
Download material for this video from below link.
drive.google.c...
Qlik - 065: Cumulative Total, Cumulative Average, Rolling n-Period (Single Dimension)
• Qlik - 065: Cumulative...
Read As of table (as of calendar) blog by Henric
community.qlik...
You can also find me on
TH-cam: / @manishkumarkachhia1683
Facebook: www.facebook.c...
LinkedIn: / manishkachhia
Twitter: / mk_qsl
Udemy: www.udemy.com/...
#qlik #qliksense #qlikview #qs #qv #qlikdatamodel #qlikscript #set #let #setvariable #letvariable #variable #newtoqlik #newtoqliksense #newtoqs #newtoqlikview #newtoqv #learnqlik #beginnerqlik #trace #tracestatement #debug #Trace #tablefunctions #qlikfunctions #qlik #fieldname #fieldnumber #nooffields #noofrows #nooftables #qvd #storeqvd #createqvd #addyears #addmonths #date #time #dateandtimefunctions #monthstart #monthend #yearstart #yearend #pick #match #customisedreport #pivottable #networkdays #sql #sqlconnection #sqldataconnection #forloop #foreachloop #qvd #createqvd #aggregation #Total #AsofTable #rolling3month #rolling

[@andreybaldyga692]

Thanks for your videos. Thanks to you, I learned to do my job much more efficiently!!!

[@farinsaifutdinov5534]

It's amazing video about brainy way to solve the task! I'm so impressed.
Dear Manish, thank you for sharing great ideas in QS.

[@shaikabdulgaffar6013]

Thanks for the creating excellent video on As of tables...

[@andreybaldyga692]

As always informative! Looking forward to new videos! I hope they will be in the future!

[@4ll_That_Jazz]

Always with great videos!

[@anu5763]

Thank you so much 👍 , finally I understood it.

[@Na-uy4ot]

This is great! I tried to create as of week as well but didn’t work. I want to track training completion rate as of each week in a month year.

[@FelipeReyes-d2w]

Hi Manish, Great video. The columns work well but in my case I would need o add the sum in the qlik script of the backend and not as measure. Do you know how I can do that?
Thanks!

[@prudhviteja9263]

what if you want to see data of rolling 12 months but from months starting jan 2021 in dimension it starts showing 15,558 instead of 230314. So, how you solve this?

[@vinu315]

Hi Manish, amazing tutorials as always. I have an issue when using the 3MA formula - with the count expression usage. There are instances when a period is missing (i.e no data for Jan'22, but data for Dec'22 and Nov'22). This causes the distinct count to return 2 instead of 3. Is the alternative to just use "3" as the count or should I be using another date field?

[@manishkumarkachhia1683]

If you have no data for any month and still you want to show data then search filling missing records, a blog by Henric on Qlik Community.

[@harikumar3786]

COULD YOU PLEASE SHARE THE MAIL ID

[@manishkumarkachhia1683]

[e-mail]

[@harikumar3786]

@@manishkumarkachhia1683 just now I have mailed you

[@harikumar3786]

Just now I have mailed you again. Please check once