6 Replies Latest reply: Jan 13, 2017 1:55 PM by Sunny Talwar

Translate Monthly YTD figures to actual Month figures

Hello Qlik gurus!

I have some data that I'm working with that stores a metric with a financial figure per month.  So it looks something like this (very simplified)

MetricDateFigure
Metric 1Aug 201610
Metric 1Sep 201625
Metric 1Oct 201635

The problem is that the number in the Figure column is cumulative for the year.  So in this example the actual month figure for Oct 2016 was not 35, it was 10.  10 for Aug, 15 for Sep and 10 for Oct adds up to 35.  I need to get the actual month figures for each month by subtracting the figure for the previous month from the target month.  So the output I want would be this:

MetricDateFigure
Metric 1Aug 201610
Metric 1Sep 201615
Metric 1Oct 201610

I know this could be fairly easily done on the presentation side,  but these numbers are used in so many places in the final dashboard that I would rather just convert the data once on the ETL side.  I've thought about looping through the original table and doing a lookup for each row to find the previous month figure.  However, this is several hundred thousand rows, and that seems really intensive.  I was looking into "Preceding Loads" as well, but can't wrap my head around how I might use it in this case.

Any other thoughts or suggestions on how I might accomplish this?  Many thanks.

• Re: Translate Monthly YTD figures to actual Month figures

May be using Peek()/Previous()

Table:

Date(Date#(Date, 'MMM YYYY'), 'MMM YYYY') as Date,

Figure;

Metric, Date, Figure

Metric 1, Aug 2016, 10

Metric 1, Sep 2016, 25

Metric 1, Oct 2016, 35

];

FinalTable:

Date,

Figure,

If(Metric = Previous(Metric) and Date = AddMonths(Previous(Date), 1), RangeSum(Figure, -Peek('Figure')), Figure) as NonCumFigure

Resident Table;

DROP Table Table;

• Re: Translate Monthly YTD figures to actual Month figures

And looking at Oleg's response made me realize that second condition in if might have to removed or changed based on the data and requirement when a month is not available in between)

Table:

Date(Date#(Date, 'MMM YYYY'), 'MMM YYYY') as Date,

Figure;

Metric, Date, Figure

Metric 1, Aug 2016, 10

Metric 1, Sep 2016, 25

Metric 1, Oct 2016, 35

];

FinalTable:

Date,

Figure,

If(Metric = Previous(Metric) and Date = AddMonths(Previous(Date), 1), RangeSum(Figure, -Peek('Figure')), Figure) as NonCumFigure

Resident Table;

DROP Table Table;

• Re: Translate Monthly YTD figures to actual Month figures

you go it right, looping through your data could be extremely slow. However, you can do the same using the functions peek() and Previous(). The two functions are similar, yet slightly different (look them up in the Help sections).

If you reload your data using a Resident load and sort the data in the correct chronological order, then you can reach into the previously loaded row and get the previous number of the Figure and subtract it from the current value of the Figure to calculate the current value:

IF( Peek('Metric') = Metric, Figure - Previous(Figure), Figure) as NetFigure

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!

• Re: Translate Monthly YTD figures to actual Month figures

Try, if the script

Directory;

Date,

rangesum (Figure,-Previous(Figure)) as Figure

FROM....

Regards

Andrey

• Re: Translate Monthly YTD figures to actual Month figures

Thank you!  I had no idea that Previous existed.  So handy.  However, now I have created a new problem for myself.  Let's say the new example data is as follows:

MetricDateFigure
Metric 1Aug 201610
Metric 1Sep 20160
Metric 1Oct 20160
Metric 1Nov 201625
Metric 1Dec 201635

The problem comes when I hit the Nov record.  If it just examines the previous month then evaluates 25 - 0, but the desired output is as follows:

MetricDateFigure
Metric 1Aug 201610
Metric 1Sep 20160
Metric 1Oct 20160
Metric 1Nov 201615
Metric 1Dec 2016

10

Is there a way to use Previous to go back an arbitrary number of records until one is found without a zero value?  I know this seems silly since it's a cumulative YTD figure (should be a figure each month), but unfortunately, if there has been no additional numbers for a month, they report nothing.

• Re: Translate Monthly YTD figures to actual Month figures

May be this:

Table:

Date(Date#(Date, 'MMM YYYY'), 'MMM YYYY') as Date,

Figure;

Metric, Date, Figure

Metric 1, Aug 2016, 10

Metric 1, Sep 2016, 0

Metric 1, Oct 2016, 0

Metric 1, Nov 2016, 25

Metric 1, Dec 2016, 35

];

FinalTable:

Date,

Figure,

NewFigure,

If(Metric = Previous(Metric) and Date = AddMonths(Previous(Date), 1), RangeSum(NewFigure, -Peek('NewFigure')), NewFigure) as NonCumFigure;

Date,

Figure,

If(Metric = Previous(Metric) and Date = AddMonths(Previous(Date), 1), If(Figure = 0, Peek('NewFigure'), Figure), Figure) as NewFigure

Resident Table;

DROP Table Table;