topic Re: YoY calculation in QlikView

YoY calculation

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

Hi,

I am using year as date field. I want to calculate previous year's value associated with some dimension.

I am using this expression

if(Year='2014',Sum({<YearField = {'$(=Year(Today()))'}>} sales),Sum({<YearField = {'$(=Year(Today())-1)'}>} sales))

I have years starting from 2014 that is why if .

But, i am still getting current year values .

Please let me know what corrections to make.

However, to calculate it for current year using

Sum({<YearField = {'$(=Year(Today()))'}>} TAM_VALUE) is giving correct results.

lech_miszkiewicz‌

stevedark‌

mto‌

Re: YoY calculation

stevedark — Mon, 13 Aug 2018 20:00:07 GMT

If your second expression works okay then the following should work to give you the previous year:

Sum({<YearField = {'$(=Year(Today())-1)'}>} TAM_VALUE)

Steve

Re: YoY calculation

Anonymous — Mon, 13 Aug 2018 20:15:20 GMT

I am getting the same value for both the expressions that is Current Year value.

Can you suggest any other expression ?

Re: YoY calculation

Lech_Miszkiewicz — Wed, 15 Aug 2018 03:58:32 GMT

Expression is correct.

Question is - what values (and datatype) do you have in YearField?

Do you have a YearField at all? It seems like you dont, hence your results are the same for both expressions...

Re: YoY calculation

MK9885 — Wed, 15 Aug 2018 12:04:43 GMT

Not sure what you trying to get but maybe try this?

if(GetSelectedCount(Year)=0,Sum({<YearField = {'$(=Year(Today()))'}>} sales),Sum({<YearField = {'$(=Year(Today())-1)'}>} sales))

If there is no Year selection you'll see your first condition or 2nd condition which means by default it will select year 2014 and satisfy first condition

Re: YoY calculation

Anonymous — Wed, 15 Aug 2018 15:37:11 GMT

I have a year column and it has year values as 2014, 2015 etc. I am confused about year and YearField.

Do I have to add YearField in the script ?

FYI, I am using year as a dimension and I have one more dimension as industry and I want to calculate measure values as growth from previous year.

Re: YoY calculation

Anonymous — Wed, 15 Aug 2018 15:39:53 GMT

I have a year column and it has year values as 2014, 2015 etc. I am confused about year and YearField.

Do I have to add YearField in the script ?

FYI, I am using year as a dimension and I have one more dimension as industry and I want to calculate measure values as growth from previous year.

Re: YoY calculation

johnca — Wed, 15 Aug 2018 15:50:45 GMT

It sounds like you just need to replace YearField with your Year field. Then maybe replace the Year dimension with Month and use something like this;

For the current year...

Label: =Max(Year)

Expression: Sum({<Year={$(=Max(Year))}>}TAM_VALUE)

For the previous year...

Label: =Max(Year)-1

Expression: Sum({<Year={$(=Max(Year)-1)}>}TAM_VALUE)

If you select a Year that is not the current year it will become the max(Year) and the previous will also be 1 less than that selected.

HTH,

John

Re: YoY calculation

Anonymous — Wed, 15 Aug 2018 15:58:28 GMT

Thanks for the explanation John.

If you see my data starts from 2014 till 2020. I have current year values . Now I want to calculate previous year values. So for 2014 it should take same values in PY field but 2014 onwards it should have one year less values

Re: YoY calculation

MK9885 — Wed, 15 Aug 2018 16:31:04 GMT

To make it simpler in your PY column use below expression....

Sum({<Year = { $(=Max(Year) -1)}>} sales)

This would always give you previous years data

Ex: if no year is selected then it will give data for 2017. If a year 2016 is selected, it will give data for year 2015.

For your CY Column you can use

Sum({<Year = { $(=Max(Year) )}>} sales)

Re: YoY calculation

Anonymous — Wed, 15 Aug 2018 16:36:27 GMT

thanks for your reply.

I want to calculate it for every year starting from 2014 till 2020 to put it in a table as shown above.

Thank you .

Re: YoY calculation

MK9885 — Wed, 15 Aug 2018 17:03:34 GMT

Convert that straight table into Pivot table, use Year dimension as Column.

It would be easier if you could provide us sample data in XL?

Sorry I do not have Qlik View full version so only data would work

Re: YoY calculation

Anonymous — Wed, 15 Aug 2018 17:44:50 GMT

Year	Industry	YoY	PY	CY	Measure
2014	Communications, Media & Entertainment	-	-	7554.78576	7565.762301
2014	Consumer Industries & Retail	-	-	5434.299709	6434.299709
2014	Energy	-	-	5867.627806	5762.627806
2014	Financial Services	-	-	82990.84794	12500.84794
2014	Government	-	-	19064.49401	13064.49401
2014	Healthcare	-	-	9858.997176	5658.997176
2014	Manufacturing	-	-	6853.38079	6853.38079
2014	Other	-	-	1990.615459	1990.615459
2014	Transportation	-	-	3490.482096	3090.482096
2015	Communications, Media & Entertainment	-	-	7650.805522	7980.805522
2015	Consumer Industries & Retail	-	-	6814.499731	6814.499731
2015	Energy	-	-	6085.464041	6085.464041
2015	Financial Services	-	-	13155.17342	13155.17342
2015	Government	-	-	13582.93441	13582.93441
2015	Healthcare	-	-	6194.286862	6194.286862
2015	Manufacturing	-	-	7153.331812	7153.331812
2015	Other	-	-	1977.288633	1977.288633
2015	Transportation	-	-	3379.063921	3379.063921
2016	Communications, Media & Entertainment	-	-	8963.848249	8963.848249
2016	Consumer Industries & Retail	-	-	7721.536388	7721.536388
2016	Energy	-	-	6982.013838	6982.013838

Re: YoY calculation

MK9885 — Wed, 15 Aug 2018 18:01:56 GMT

Please put your raw data into an XL file and attach it...

Re: YoY calculation

Anonymous — Wed, 15 Aug 2018 18:18:42 GMT

I dont see any option to attach an excel file.

Re: YoY calculation

MK9885 — Wed, 15 Aug 2018 18:33:30 GMT

When you click reply, on top right you'll see 'Use Advance editor'

Click than and upload your data...

With whats your sample expression and expected output...

Thanks.

Re: YoY calculation

Anonymous — Wed, 15 Aug 2018 18:38:06 GMT

I am trying to find that option but I am unable to see it. I just have an option to add image or media, links .

Thanks.

Re: YoY calculation

trdandamudi — Wed, 15 Aug 2018 18:43:21 GMT

Don't reply from within your Inbox, because you will not see that option. Click Reply from outside the Inbox and you will see the below screen with the option "Use advance editor" and when you click on this you can attach a file:

Re: YoY calculation

juraj_misina — Wed, 15 Aug 2018 20:14:18 GMT

Hi Ronit,

I think what you are trying to do is to manipulate dimension value with set analysis, which is not possible. Set analysis is evaluated before a chart is calculated. So this means that you are not able to get data from previous year on this year's row (because intersection of data set for, say current year 2018 and data for previous year 2017 is an empty set, hence no data to aggregate).

There could be a way to achieve this using the Above() function, but that's a tricky one.

Hope this helps.

Juraj

Re: YoY calculation

Anonymous — Wed, 15 Aug 2018 20:27:18 GMT

Yea thats why I was putting if condition so that when it selects 2014 the previous year should be 2014 and for 2014 onwads it should be one less .

my data set only has ----- Year, Industry as Dimensions

and value - as measure

CY and PY are calculated fields