YoY calculation

[Anonymous]

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‌

[stevedark]

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

[Anonymous]

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

Can you suggest any other expression ?

[Lech_Miszkiewicz]

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...

[MK9885]

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

[Anonymous]

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.

[Anonymous]

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.

[johnca]

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

[Anonymous]

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

[MK9885]

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)