12 Replies Latest reply on Sep 14, 2015 2:04 AM by shripad nadiger

    Calculation condition not working

    shripad nadiger

      Hi All,

      I am stuck up with a problem related to calculation condition.

      I have created a bar chart which shows 'Sum of values' of the dimension.

      The requirement is when I select particular dimension from the quick filter, its should show up the values corresponding to that particular dimension and when I select all the dimensions the chart should show up values of a totally new dimension.

       

      I have created a following calculation for the same.

       

      "IF ATTR([Qoc parameters])="Activation Index" OR ATTR([Qoc parameters])="Coverage Index" OR ATTR([Qoc parameters])="Manpower Index" OR ATTR([Qoc parameters])="Process Index"

      THEN AVG([Qoc parameter value])

      ELSE AVG([Qoc Index])

      END

      "

       

      Where 'Qoc Parameter' is my dimension and 'Qoc parameter value' is the aggregation of the values.I am using 'QoC parameter' as a filter.

       

      The problem with this calculation is that, whenever I select 2 or 3  dimensions in Quick filter it ends up showing 'Qoc Index' which I need to show only if i select all the four dimensions.

       

      Thanks and Regards,

      Shripad

        • 1. Re: Calculation condition not working
          Sankarmagesh Rajan

          Hi Shripad

          Try this

           

          If you have index dim and you have index parameter in wb. Add All in parameter list.

           

          if [Index dim]=[index parameter] then AVG([Qoc parameter value])

          else AVG([Qoc Index])

          END

          • 2. Re: Calculation condition not working
            shripad nadiger

            Sankarmagesh Rajan

             

            By using a parameter as a filter, I will not be able to do a multi select and hence the condition will not work.

             

            Regards,

            Shripad Nadiger

            • 3. Re: Calculation condition not working
              Vladislav Grigorov

              Shripad,

               

              Have a look at how this can be done using LoD calcs here. I am making use of the order of operations for FIXED and EXCLUDE LoD calcs. FIXED precedes regular dimension filters, while EXCLUDE is executed after the dimension filters in the Tableau pipeline. Thus I am checking if all values are selected in your multi-select filter, to be able to show AVG of a different measure depending on the selection. The formula I used is:

               

              IF MAX({exclude [LOB]: countd([Qoc parameters])}) != MAX({fixed: COUNTD([Qoc parameters])})

              THEN AVG([Qoc parameter value])

              ELSE AVG([Qoc Index])

              END

               

              Note that it is dependent on the dimensions in your view, as it excludes [LOB].

               

              Hope this helps,

               

              Vlad

              • 4. Re: Calculation condition not working
                shripad nadiger

                @Vladislav Grigrova,

                 

                Thanks a ton..!!!

                It works, I will surely learn more about LOD's as well.

                Just a small question, I was tweaking the above formula so as i can get previous month data as well on my original dashboard.

                 

                IF ATTR(Month([Date]))=7

                THEN

                (IF MAX({exclude [LOB]: countd([Qoc parameters])}) != MAX({fixed: COUNTD([Qoc parameters])})

                THEN AVG([Qoc parameter value])

                ELSE AVG([Qoc Index])

                END)

                END

                According to my original data, I need to consider 'July' as previous month but with I am unable to pull any data with this formula.


                Am I missing here something.


                Thanks and Regards,

                Shripad Nadiger

                • 5. Re: Calculation condition not working
                  Vladislav Grigorov

                  Shripad,

                   

                  post a packaged workbook, so that one could have a look at what you want to achieve, and how your data is structured. I do not see any date field in your original data, and there might be potentially different approaches to this depending on your data. I believe the final solution may include something like shifting the original date with the period for comparison, then possibly coloring by the shifted date.

                   

                  For sure using ATTR(Month([Date]))=7) would not work in your case as you do not have any notion of month in your view.

                   

                  Best,

                   

                  Vlad

                  • 6. Re: Calculation condition not working
                    shripad nadiger

                    Vlad,

                    By original data I meant the live production data that I am using in office while the one I am using here is the test data I created for demonstration purpose which does not have a Date field..:)

                    Coming back to the problem,[Check 'Sheet 2']

                    The formula you gave works perfectly fine and I am getting Avg([Qoc parameter value]) when i select 3 values in filter and getting AVG([Qoc Index]) when I select all the 4 values in filter.

                    I want to replicate the same formula such that only shows me data for the month of 'June'.

                    For that, I tweaked your formula and added an additional IF clause.

                     

                    IF ATTR(Month([Date]))=6

                    THEN

                    (IF MAX({exclude [LOB]: countd([Qoc parameters])}) != MAX({fixed: COUNTD([Qoc parameters])})

                    THEN AVG([Qoc parameter value])

                    ELSE AVG([Qoc Index])

                    END)

                    END

                     

                    For this formula to work fine I need to add MONTH([Date]) field on the rows but If i do this I am getting blank spaces for the month of 'July' which is not desired.

                    Is there a way, I can get the last month data without the addition of MONTH([Date]) field on the rows or may be hide the blank row's somehow.

                     

                     

                    Thanks,

                    Shripad

                    • 7. Re: Calculation condition not working
                      Vladislav Grigorov

                      Shripad,

                       

                      I think you're heading towards a month-over-month calculation or some comparison of the sort. I changed a bit your sheet 2 to show the continuous MONTH([Date]), and then made the pill discrete - have a look at sheet 2 here. This will allow Tableau to handle months from different years properly.

                      Then I created another calculation that makes use of table calcs:

                      [Previous month data] = lookup([Calculation1],-1)

                      What this actually does is to return the value of [Calculation1] for the previous partition (for more info on table calcs, namely the concepts of addressing and partitioning, there are tons of resources out there, but I'd recommend Tom Brown's primer here or some of the great posts of Johnathan Drummey over there). Putting this in the measure values container and setting it's addressing to Date gives you the result you desire - for every month in your view you see the result of your [Calculation1] for the previous month.

                      Then because now MONTH([Date]) is also in the view, I tweaked a bit the initial formula for [Calculation1]:

                       

                      IF MAX({exclude [LOB],[Date]: countd([Qoc parameters])}) != MAX({fixed: COUNTD([Qoc parameters])})

                      THEN AVG([Qoc parameter value])

                      ELSE AVG([Qoc Index])

                      END

                       

                      This change would allow for proper comparison to make sure which metric to show.

                       

                      Finally, to only show the last row of data, I created another calculation:

                      [hide old months] = lookup(ATTR(DATETRUNC('month', [Date])),0)

                      placed it on the filter shelf, set the compute using to Table [Date], and selected a relative filter at the month level to show only the last 3 months. Now as we are in September it is only showing July - probably in your final implementation you will leave this to only the last month or the current month depending on your situation. This table calculation filter is effected late in the Tableau pipeline giving the chance for other calcs to compute properly. In effect it only hides previous month, not filtering out the data completely, which would otherwise make your [Previous month data] show only Nulls.

                       

                      To clean up a bit, I just removed the "show header" checkmark from the month([Date]) pill, and there you go.

                       

                      Best,

                       

                      Vlad

                      1 of 1 people found this helpful
                      • 8. Re: Calculation condition not working
                        shripad nadiger

                        Vlad,

                        You are a genius..Thanks a lot. I exactly got what I wanted. I have tested the solution on the test database and it's working absolutely fine. I will go back to office on Monday and test it on production data.

                        Thanks again...not only did i find a solution but learnt a lot from you as well...:)

                         

                        Regards,

                        Shripad

                        • 9. Re: Calculation condition not working
                          Vladislav Grigorov

                          Glag it helped! Take care!

                           

                          Sent from my iPhone

                          • 10. Re: Calculation condition not working
                            shripad nadiger

                            Vlad,

                            Sorry to bug you again, but I just made this observation in the application.

                             

                            Check Sheet2 : https://public.tableau.com/profile/vladislav.grigorov5152#!/vizhome/Book12_98/Sheet2

                            If I select , 'Activation Index' in the filter.

                            and check the 'calculation1' column, which display's aggregated data of all the months. The values seem to decrement by 1.

                            For example: Select filter Activation Index and then look at the 'calculation1' column,

                            For Bus the value is coming as 7 and for car its showing 11.

                            Whereas, the actual result should be 8 and 10 respectively.

                             

                            I removed the 'Month([Date])' pill from the rows and also removed the 'hide old months' from the filter. The numbers are showing up currently but then my previous month calculation goes for a toss since its dependent on 'Month[Date])'.

                             

                            Regards,

                            Shripad

                            • 11. Re: Calculation condition not working
                              Vladislav Grigorov

                              Shripad,

                               

                              Have a look at this:

                              Untitled.png

                              I color-coded the numbers that you are seeing, to be able to explain better.

                              First of all according to the formula for [Caclulation1], as you have selected only "Activation Index" in the quick filter, the numbers should match the averages for Qoc parameter value. Now these averages are over a single record, as you can see, so they in fact match the record level values, but in the general case [Caclulation1] would match AVG([Qoc parameter value]) at the level of detail of the view, namely [LOB], Month([Date]). Therefore the figures you see, and I believe this is expected behavior.


                              To get to 8 and 11 (which are averages of the monthly averages) you can either use table calculations, or LoDs, or maybe even blending. I felt like going with the table calcs option, so created [Calculation2] like this:


                              IF MAX({exclude [LOB],[Date]: countd([Qoc parameters])}) != MAX({fixed: COUNTD([Qoc parameters])})

                              THEN WINDOW_AVG(AVG([Qoc parameter value]))

                              ELSE WINDOW_AVG(AVG([Qoc Index]))

                              END

                               

                              The only difference from your original [Calculation1] are the WINDOW_AVG() wrappers in blue, as you can see. When you drag [Calculation2] into the view, and set its compute using to [Date], you get the following result:

                              Untitled.png

                              Is this what you're after? I suggest also have a look at some of the many available resources explaining the various levels at which a calculation can be made in Tableau. These may be record level (e.g. [measure1]+[measure2]), VIZ level (e.g. SUM([measure1])+SUM([measure2])), other-than-VIZ level (can be achieved via Table calcs, blending, LoD calcs). I do not have handy a link to a such explanation, but as always, you could refer to Johnathan Drummey's blog.

                               

                              Hope this helps,

                               

                              Vlad

                              • 12. Re: Calculation condition not working
                                shripad nadiger

                                Vlad,

                                Thanks for your assistance. This is certainly the same what I am looking for..:), I will surely go through the blogs to get a hold on the calculations and the way they work.

                                 

                                Regards,

                                Shripad Nadiger