Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Simply create YTD, moving totals and comparisons versus Year Ago

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Simply create YTD, moving totals and comparisons versus Year Ago

Attachments


When we want to do something with QlikView, we face so many possibilities to achive the goal. And sometimes, the first idea we have is not necessarily the simplest one.

I wanted to do create YTD and moving totals measures, difference (absolute and in percentage) of these moving totals versus the Year ago. QlikView is a BI tool: no problem I thought. Well, it was not so simple ...   The expressions became so complex that I finally did not understand them any more!

After some searches and many tries,the solution to such a chart is very simple. You just need to look at the right place.

As you can see, this chart contains actual data and the one of the year ago, a YTD starting in January and the same for the Year Ago, Rolling 3 and 12 periods and the same for the Year Ago. These values are contained in a TIME ANALYSIS axis that the user can select in a simple List Box to display only YTD and Rolling12 for example:

MovingTotals.JPG

The document will help you to understand the modification you have to do to your data model to make this report very simple. As you can already see, the expression is the simplest expression in QlikView: sum(VALUE).

This simplicity let you afterwards mix the different measures of the database with this TIME ANALYSIS axis : get the current sales values, but the share the products represents the last 12 motnhs, and a difference vs year ago:

MovingTotals2.JPG

Many thanks to G. Wassenaar and his post that helped me to understand where to look exactly :  http://community.qlik.com/docs/DOC-4252

Have a nice reading

Fabrice AUNEZ

I have attached sample QVW and Sample data. Hope this will be helpful for all..
Comments
fashid
Specialist
Specialist

Hi,

Could you upload a QVW of the same

0 Likes
Not applicable

Dear Aunez,

I tried to create the qvw as per your document and getting Script line error while running the Macros in script or edit module.

Capture.JPG

Script I used is,


LET vStartDate = Num(AddMonths(YearStart(Today()),-24));
LET vEndDate = Num(AddMonths(YearEnd(Today()), 0));

PERIODT:


Load Distinct
  Date(MonthEnd(($(vStartDate) +(Iterno()-1))), 'MMM-YYYY') as PERIOD
Autogenerate(1)
While
Date($(vStartDate)+(Iterno()-1)) <=Date($(vEndDate));

PERIOD:
Load Distinct RowNo() as PERIOD_KEY, PERIOD,

Year(PERIOD) as YEAR,
Month(PERIOD) as MONTH

resident PERIODT order by PERIOD
;

DROP table PERIODT;
TYPE:
LOAD TYPE_KEY,
     [TIME ANALYSIS]
FROM
[TYPE.xlsx]
(ooxml, embedded labels, table is Sheet1);

call LOAD_nAGO('CURRENT', 0);
call LOAD_nAGO('P-1', 1);
call LOAD_nAGO('Y-1', 12);
call LOAD_MVTOT ('MVTOT3', 3, 0);
call LOAD_MVTOT ('MVTOT12', 12, 0);

call LOAD_MVTOT ('MVTOT3 Y-1', 3, 12);
call LOAD_MVTOT ('MVTOT12 Y-1', 12, 12);
call LOAD_YTD('YTD', 0);
call LOAD_YTD('YTD-1', 12);


SUB LOAD_nAGO(vTypeTemporel, vOffset)
REL_PER_TIME:
LOAD PERIOD_KEY,
PERIOD_KEY-$(vOffset) as TIME_KEY,
'$(vTypeTemporel)' as TYPE_KEY
RESIDENT PERIOD
where PERIOD_KEY > $(vOffset);
End sub


SUB LOAD_MVTOT(vTypeTemporel, vNbPeriods, vOffset)
// F. AUNEZ, sep 2013
// Population of the relation PERIODE - TIME
// Creation of the moving totals (and averages ...)
//
Concatenate (REL_PER_TIME)
LOAD
PERIOD_KEY,
PERIOD_KEY + 1 - IterNo()-$(vOffset) as TIME_KEY,
'$(vTypeTemporel)' as TYPE_KEY
Resident PERIOD
while IterNo() <= $(vNbPeriods) AND PERIOD_KEY >= $(vNbPeriods)+$(vOffset)
;
END SUB

SUB LOAD_YTD(vTypeTemporel, vOffset)
// F. AUNEZ, sep 2013
// Population of the relation PERIODE - TIME
// Creation of the YTD
//
//June 2013 (key 18) will totalize from January (Key 13) to June (Key 18)
// For the YTD Year Ago, same totalization but for the Year Ago (12 keys back)
// we will use Iter() in order to loop back until Janyary (Key 1, 13, 25 ...)
// that what the div( ....) part does
Concatenate (REL_PER_TIME)
LOAD
PERIOD_KEY,
PERIOD_KEY + 1 - IterNo() - $(vOffset) as TIME_KEY,
'$(vTypeTemporel)' as TYPE_KEY
Resident PERIOD
while PERIOD_KEY - $(vOffset)-IterNo()+1 >= div( if( mod(PERIOD_KEY- $(vOffset),
12)=0, PERIOD_KEY- $(vOffset)-1, PERIOD_KEY- $(vOffset)), 12)*12 +1
;
END SUB

BR

Neeru.


0 Likes
Anonymous
Not applicable

Muito legal este material.

Obrigada.

0 Likes
flanfranco
Partner - Contributor III
Partner - Contributor III

Thanks!!!
Great Post!

0 Likes
Anonymous
Not applicable

Thanks, great post.

Is there anyone of you, who already added the functionality to calculate rolling periods on day basis as well?

E.g. to calculate 12 month cumulated sales from 20.08.2016 to 20.08.2015

This would be an awesome feature.

0 Likes
ysj
Creator
Creator

Great Article

0 Likes
Anonymous
Not applicable

Hi Experts

This is a great article and I can make use to show Month to Date MTD value and Previous Month to Date value easily.

I am trying to figure out to create another field to show the difference of amount between Current Month MTD and Last Month MTD Value. Could not figure out since Set Analysis expression not allowed in Loading Script and as table used in this example "REL_PER_TIME" is saving Period ID only, not the values.


Any help would be much appreciated. Thanks

0 Likes
vikasmahajan

vikasmahajan_0-1652022028982.jpeg

Can you please attach QVW if possible ?

I need to implement same logic for my dashboard

Many Thanks

Vikas

 

 

0 Likes
Version history
Last update:
‎2013-09-17 05:15 AM
Updated by: