Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
mto
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
I am getting the same value for both the expressions that is Current Year value.
Can you suggest any other expression ?
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...
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
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.
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.
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
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
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)