9 Replies Latest reply on Aug 24, 2016 10:04 PM by naveen.yanamadala

    calculated logic

    naveen.yanamadala

      Hi,

      I need all your help to create a logic/calculated field for my chart

       

      In the  below chart #1:  The 2 lines showing are transaction% and Amount % fields . I have bank field as a muti value dropdown filter with values as : All, A,B,C, D E,F,G.H..

      If we select  multiple banks say like A and B then i should get 4 lines and also if i select bank C then i should get 6 lines  as shown in chart #2 ( I achieved this by keep bank field in color shelf

       

      My requirement is if i select ALL from the bank filter  then I need to get only 2 lines with the total of all banks A,B,C, D E,F,G.H..

       

       

      Please let me know your thoughts .

      Thanks in advance.

       

      chart#1

      Chart-1.PNG

       

       

      Chart #2:

      chart2.PNG

        • 1. Re: calculated logic
          Naveen Agarwal

          When you use the Bank Field on the Color Mark, it will show one line each for all of the selected banks. I don't think you can avoid showing all banks when you select ALL in the filter.

           

          If you are looking to aggregate the two fields (Transaction % and Amount %) when all Banks are selected, then you will need to setup a calculated field. You can then create a separate chart for the aggregated value.

           

          What is on your x-axis in these charts?

           

          If you can post your data, it will be helpful.

          • 2. Re: calculated logic
            naveen.yanamadala

            Hi Naveen,

             

            Thanks for the reply. Could you please let me know how the logic should be written for the calculated field for all banks and is there anyother way to show single multiple and all selection in the same chart.

             

             

            Thanks,

            • 3. Re: calculated logic
              Jessica Murray

              Unfortunately, the filter alone cannot give you this functionality, but using a parameter you can get approximately what you are after.

               

              As in the attached workbook, create a parameter to switch between a comparison mode, in which all banks will have two lines for your two measures, and a total mode, in which there will be two lines which represent the total across all selected banks.

               

              Then create a calculated field for each of your measures, like below for 'Amount'.

              IF [Bank parameter] = 'Total'

                  THEN {EXCLUDE [Bank] : SUM([Amount])}

              ELSE {INCLUDE [Bank] : SUM([Amount])}

              END

               

              Basically the field is switching between providing sums for an individual bank and a sum over all the banks, dependant on the choice of the parameter. The {}s indicate a Level of Detail (LOD) calculation. If you are not familiar with them, they are wonderfully useful, and summarised well here: Overview: Level of Detail Expressions

               

              In implementing this chart, I would also recommend:

              • Having a calculated field for the tooltip, so that if 'Total' is selected, the tooltip will read 'All banks'; and
              • Having dual and synchronised axis (you can hide the right-hand one by making it white, if you want).

               

              Hope this helps, do let me know if you have any questions!

              1 of 1 people found this helpful
              • 4. Re: calculated logic
                Naveen Agarwal

                I tried this method and it seems to work. But you have to select ALL banks in the Bank filter when selecting "Total" as the parameter. Otherwise, it does not aggregate the right values.

                 

                I wonder if there is a way to link the Parameter values to the Filter selection. If ALL is selected, then the Parameter should automatically be selected as "Total". If not, then the other parameter value should be selected.

                • 5. Re: calculated logic
                  Luciano Vasconcelos

                  Parameters don't have this behavior. They can't be changed by actions.

                  • 6. Re: calculated logic
                    naveen.yanamadala

                    Hi Jessica,

                     

                    Thanks for the reply......  I am trying to make my chart with the changes mentioned by you but I am unable to open the  workbook since i am using  version 9.2

                    • 7. Re: calculated logic
                      Jessica Murray

                      Ah, that's a pain. In that case, to set up what was in the workbook:

                      • Create a parameter, e.g. 'Bank parameter', and give it two possible values, e.g. 'Total' and 'Comparison'
                      • Show parameter control (from the context menu by right-clicking the newly created parameter
                      • Create a calculated field for each of your measure values (i.e. transaction and amount), like the following:

                      IF [Bank parameter] = 'Total'     THEN {EXCLUDE [Bank] : SUM([Amount])}ELSE {INCLUDE [Bank] : SUM([Amount])}ENDThen set up the chart:

                      • Your x-axis in the columns shelf
                      • The new calculated Amount and Transaction fields in the rows shelf, each aggregated by SUM
                      • Make Amount and Transaction dual axises (see below) and synchronise them (by right-clicking the right-hand axis and selecting from the menu)
                      • Untick 'Show header' on the right-hand axis
                      • Add a bank filter and 'Show filter'

                      Screen Shot 2016-07-25 at 18.26.39.png

                      Beyond aesthetic stuff ('Measure names' are there as a label as without it amount and transaction were indistinguishable on the 'Total' mode, and there's also the calculated tooltip mentioned before) that should be it.

                      1 of 1 people found this helpful
                      • 8. Re: calculated logic
                        naveen.yanamadala

                        Hi Jessica,

                         

                        I tried the below logic as u said and having the below error

                         

                        IF [Bank parameter] = 'Total' THEN {EXCLUDE [BANK_NAME] : SUM([CUM_FRAUD_AMT %])}

                        ELSE {INCLUDE [BANK_NAME] : SUM([CUM_FRAUD_AMT %])}END

                         

                        error: Argument to sum(an aggregate function) is already an aggregation, and cannot be further aggregated

                         

                        Note: Amt and tran fields are cumulative fields

                        and the formula which i used to create cum_fraud_amt% is : running_sum(sum([fraud_amt])) / Total(sum([Fraud_amt]))

                        Please help on this

                        • 9. Re: calculated logic
                          naveen.yanamadala

                          Hi Jessica,

                           

                          I still couldn't crack the  issue which i posted in the above post . I am still struggling to get a logic for the cumulative field i am using.

                          Please help me out

                           

                          Thanks,