7 Replies Latest reply on Apr 2, 2018 12:22 AM by David Maning

# Calculate difference between prev and parameter year

Hello community,

I would like to calculate difference between parameter year (Year::Parameter) and previous year and show arrow how to change the value (up, down, stable). In the Columns should be visible only year which was selected from Parameter Year but when was selected min Parameter year (2013) it should be show data from 2013 year without caparison with prev year due to no previous data.

Year::Parameter = 2015

Year::Parameter = 2013

• ###### 1. Re: Calculate difference between prev and parameter year

Hi,

Please guide me through your calculations. There is a mistake in KPI calc (may  be).

Pease find workbook attached.

D

• ###### 2. Re: Calculate difference between prev and parameter year

Hi Szymon,

I think that you require much more knowledge in Tableau calculations to resolve such issues like this. There were a lot of mistakes. I tried to highlight them for you.

Anyway the solution is following:

IF ({ FIXED [Site],[LMU name]: SUM(

IF [PivotYear]=[Year::Parameter]

THEN [Demand Value]

END)}

/

{ FIXED [Site], [LMU name]: SUM(

IF [PivotYear]=[Year::Parameter]

THEN ZN([PivotValue])

END)}) > 0.6

THEN 1

ELSE 0

END

IF ({ FIXED [Site],[LMU name]: SUM(

THEN [Demand Value]

END)}

/

{ FIXED [Site], [LMU name]: SUM(

THEN ZN([PivotValue])

END)}) > 0.6

THEN 1

ELSE 0

END

3) Indicator corrected

IF [Year::Parameter]!= {MIN([PivotYear])}

THEN

IF { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU])} - { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU_Prev_year])} > 0

THEN 'Up'

ELSEIF { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU])} - { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU_Prev_year])} < 0

THEN 'Down'

ELSE '0'

END

ELSE STR({ FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU])})

END

Done.

Trust this helps.

D

• ###### 3. Re: Calculate difference between prev and parameter year

It's impossible to downgrade the version of workbook. Anyway I wrote everything in my previous response.

Pls mark it correspondingly and close the topic!

Thanks.

D

• ###### 4. Re: Calculate difference between prev and parameter year

Hi Dimitriy

Thank you for explanation but what i need to create is comparison what is difference between CNT of Alerts from chosen year from parameter year and previous year which was chosen from parameter year.

For me important is how to find the difference. In "Diff Prev vs Curr" Sheet you can see correctly calculation (I use Quick caluculation - difference option) but when use FilterDateRange with True value the value are not visible. Additionally when i choose 2013 from parameter year there should be initially value.

• ###### 5. Re: Calculate difference between prev and parameter year

I did exactly what you are looking for.

As I stated you have mistakes in your calculations. So your work is computed wrong or just want something else from what you have declared.

For example, have a look at those values:

As you can see, you KPI calculation is wrong. Basically, how will you get 101% from dividing 4k / 10k? =)https://www.screencast.com/t/FqRtwAiiklm;

Now I'd like to ask you again to follow this topic So Your Question Didn't Get Answered...  and mark my responses correspondingly. Then I will provide with screenshots  of how to apply my calculations correctly.

D

• ###### 6. Re: Calculate difference between prev and parameter year

Exactly what you declared in your original post.

• ###### 7. Re: Calculate difference between prev and parameter year

Okey,

So here we go.

1) We count the number of alerts for the year selected in parameter:

IF ({ FIXED [Site],[LMU name]: SUM(

IF [PivotYear]=[Year::Parameter]

THEN [Demand Value]

END)}

/

{ FIXED [Site], [LMU name]: SUM(

IF [PivotYear]=[Year::Parameter]

THEN ZN([PivotValue])

END)}) > 0.6

THEN 1

ELSE 0

END

2) We count the number of alerts for the previous year:

IF ({ FIXED [Site],[LMU name]: SUM(

THEN [Demand Value]

END)}

/

{ FIXED [Site], [LMU name]: SUM(

THEN ZN([PivotValue])

END)}) > 0.6

THEN 1

ELSE 0

END

3) We figure out numbers for the visualization:

IF [Year::Parameter]!= {MIN([PivotYear])}

THEN { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU])} - { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU_Prev_year])}

ELSE { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU])}

END

4) We figure out shapes for the visualization:

IF [Year::Parameter]!= {MIN([PivotYear])}

THEN

IF { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU])} - { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU_Prev_year])} > 0

THEN 'Up'

ELSEIF { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU])} - { FIXED [Site]: SUM([CNT_Alerts_Corrected _LMU_Prev_year])} < 0

THEN 'Down'

ELSE '0'

END

ELSE ''

END

5) Shapes applying:

6) Applying blind shape for min year and coloring it in white:

Done.