
1. Re: Calculate difference between prev and parameter year
David Maning Mar 31, 2018 4:10 PM (in response to szymon.j)Hi,
Please guide me through your calculations. There is a mistake in KPI calc (may be).
Pease find workbook attached.
D

Diff_Prev_Curr_10.3.twbx 93.9 KB


2. Re: Calculate difference between prev and parameter year
David Maning Apr 1, 2018 1:07 AM (in response to szymon.j)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:
1) CY alerts count:
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) PY alerts count:
IF ({ FIXED [Site],[LMU name]: SUM(
IF [PivotYear]=DATEADD('year',1, [Year::Parameter])
THEN [Demand Value]
END)}
/
{ FIXED [Site], [LMU name]: SUM(
IF [PivotYear]=DATEADD('year',1, [Year::Parameter])
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.
Please follow and read this topic to mark my response as correct and helpful correspondingly. So Your Question Didn't Get Answered...
Please find updated workbook attached.
Trust this helps.
D

Diff_Prev_Curr_10.3.twbx 101.8 KB


3. Re: Calculate difference between prev and parameter year
David Maning Apr 1, 2018 1:29 AM (in response to szymon.j)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
szymon.j Apr 1, 2018 1:47 AM (in response to David Maning)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.

Diff_Prev_Curr_10.3 (1).twbx 96.2 KB


5. Re: Calculate difference between prev and parameter year
David Maning Apr 1, 2018 2:27 AM (in response to szymon.j)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
David Maning Apr 1, 2018 2:42 AM (in response to szymon.j) 
7. Re: Calculate difference between prev and parameter year
David Maning Apr 2, 2018 12:22 AM (in response to szymon.j)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(
IF [PivotYear]=DATEADD('year',1, [Year::Parameter])
THEN [Demand Value]
END)}
/
{ FIXED [Site], [LMU name]: SUM(
IF [PivotYear]=DATEADD('year',1, [Year::Parameter])
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.
Pls mark as helpful.
D