topic Re: Previous 2 Months Value in QlikView

Previous 2 Months Value

scarr1980 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I'm new to Qlikview and have looked at other solutions on the forum but cannot find one that works for my data and would appreciate some guidance towards a solution.

At this stage all that I am trying to accomplish is to append the previous 2 months data as 2 new calculated dimensions/expressions to the table.

I have the following example dataset (which continues on over months as per the AsAtDate column):

What I have:

KPI Title	KPI Number	Reporting Freq	Location	Target/EA YTD	Result YTD	% Var YTD	AsAtDate
% of Facebook leads converted	MH1	M	Dublin	90%	90.8%	0.93%	01/01/2018
% of Facebook leads converted	MH1	M	London	90%	91.5%	1.69%	01/01/2018
% of Facebook leads converted	MH1	M	New York	90%	99.7%	10.81%	01/01/2018
% of Facebook leads converted	MH1	M	Tokyo	90%	95.8%	6.39%	01/01/2018
% of instagram ebook signups	MH2	M	Dublin	75%	91.8%	22.37%	01/01/2018
% of instagram ebook signups	MH2	M	London	75%	70.6%	-5.88%	01/01/2018
% of instagram ebook signups	MH2	M	New York	75%	61.0%	-18.69%	01/01/2018
% of instagram ebook signups	MH2	M	Tokyo	75%	53.8%	-28.33%	01/01/2018

What I would like to achieve:

KPI Title	KPI Number	Reporting Freq	Location	Target/EA YTD	Result YTD	% Var YTD	AsAtDate	Current (-2)	Current (-1)
% of Facebook leads converted	MH1	M	Dublin	90%	90.8%	0.93%	01/01/2018	92.7%	94.1%

Re: Previous 2 Months Value

chrismarlow — Mon, 24 Jun 2019 14:32:15 GMT

Hi,

I would be inclined to set 3 variables, with the prior months calculated from the first;

Then use set analysis to pick out the months in the expressions (I've just shown the three values for simplicity);

Note my dates are DD/MM/YYYY - I think this approach would work if you are on different settings, but if it does not work would be the first thing I checked.

Cheers,

Chris.

Re: Previous 2 Months Value

scarr1980 — Thu, 27 Jun 2019 15:22:11 GMT

Hi Chris,

Thanks for the reply. What you posted was very helpful and has helped me move a bit further along.

I still have a couple of queries:

In your tables -your 'Target /EA YTD' is only applied once =- mine is coming in on every line. Why would that be?

I have created the Variables and used them in the expressions you outlined but in my Straight Table the result from the expression is being applied on a new line rather than on the same line as desired.

Thanks again for the help I'm still trying to mentally wrap my head around the mechanics of it all.

Re: Previous 2 Months Value

chrismarlow — Thu, 27 Jun 2019 18:01:31 GMT

Hi,

I think that is because you have more dimensions than me;

I would add the other columns in as expressions - eg only({<AsAtDate={'$(vCurrent)'}>}[Target/EA YTD]) as [Target/EA YTD] etc.

But this is going to limit you to showing 1 month - if you want to show more than one month (on a row basis) with history in columns will need to do something different.

Cheers,

Chris.

Re: Previous 2 Months Value

scarr1980 — Fri, 28 Jun 2019 08:15:36 GMT

Hi Chris,

Thanks again and I hope it didn't hurt when you fell from heaven.

Seriously though, much appreciated. Thats given me enough for now to move it forward a little.

I'm sure I'll be back with another thread begging for help in now time.

Re: Previous 2 Months Value

chrismarlow — Fri, 28 Jun 2019 12:47:07 GMT

No worries - glad to help get you going in the right direction.