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

    Calculate difference between prev and parameter year

    szymon.j

      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
          David Maning

          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
            David Maning

            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

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

              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

                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
                  David Maning

                  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:

                  2018-04-01_1220

                  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

                    2018-04-01_1239

                     

                    2018-04-01_1241

                     

                    Exactly what you declared in your original post.

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

                      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

                       

                      Capture.PNG

                       

                      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

                       

                      Capture.PNG

                       

                      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

                       

                      Capture.PNG

                       

                      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

                       

                      Capture.PNG

                       

                      5) Shapes applying:

                      Capture.PNG

                       

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

                      Capture.PNG

                      Done.

                       

                      Pls mark as helpful.

                       

                      D