2 Replies Latest reply on Oct 22, 2018 3:31 PM by Alex Kerin

    Text Summary Fields, LoD, and why does this work?

    Alex Kerin

      I am becoming a bigger fan of text summaries on dashboards - e.g "Sales this week were x in Massachusetts, compared to z in Indiana". This is easy to do with non-aggregated measures, or aggregated measures that you can easily reproduce - e.g.

       

      Total sales

      "Profit this week was "+ sum(if [state="MA" then [sales] end)+ "% in Massachusetts, compared to + sum(if [state="IN" then [sales] end)+"% in Indiana"

       

      or for a 'filtered' profit replace the inner calculations with sum(if [state="MA" then [sales] end)/sum(if [state="MA" then [costs] end)

       

      However, with complex aggregated calculations I have no desire to recreate the aggregated calculation within with the text summary with if/thens peppered throughout - it gets far too long and is prone to errors.

       

      Enter LoDs..

       

      I suspected it could be done, and it seems it can be, BUT, I have no idea why it works and what potential errors could crop up.

       

      See the attached, but basically the calculation is:

       

      "In Mass, the profit ratio was "+str(max({fixed [State]="Massachusetts":[Profit Ratio]}))

      + ". In Indy, the profit ratio was "+str(max({fixed [State]="Indiana":[Profit Ratio]}))

       

      If I change that max to avg, it's wrong. Don't know why. Honestly, I'm not even really sure what I'm fixing in the LoD and what could go wrong.

        • 1. Re: Text Summary Fields, LoD, and why does this work?
          Michel Caissie

          Alex,

           

          When you do

          {fixed [State]="Massachusetts":[Profit Ratio]}

          You actually group by  a dimension defined by [State]="Massachusetts"

           

          If you create a calculation [State]="Massachusetts" , you can see on sheet3  that this dimension have 2 values, true and false.

          This means that {fixed [State]="Massachusetts":[Profit Ratio]} returns  2 profit ratio, one for true (Massachusetts) and one for false (all other states).

          So obviously, if you aggregate those two values, you will get  a different result depending on the aggregation.

          You can see the result on sheet3(2),  which is a duplicate of sheet3 but without the dimension [State]="Massachusetts" in the view.

           

          What you want, is first get the profit ratio per state with

          {FIXED [state]: SUM([Profit])/SUM([Sales])}

           

          and then do a calculation per State

          if [State]="Massachusetts" then [Profit Ratio per State] end

          You can see on sheet5 , that Min,Max or avg returns the same value, because you have a single value, the other states returning null.

           

          Now to get rid of the null, you can encapsulate this in another fixed lod in your final string, which will look  like

          "In Mass, the profit ratio was "+str({FIXED: MIN([Profit Ratio for Mass])})

          see sheet6

          Here you could use MIN,MAX, and AVG and still get the same result.

           

          edit:

          actually in your example you could simplify with "In Mass, the profit ratio was "+str( MIN([Profit Ratio for Mass])

           

          the difference is

          "In Mass, the profit ratio was "+str({FIXED: MIN([Profit Ratio for Mass])})

            returns the value for each row of the dataset

           

          "In Mass, the profit ratio was "+str( MIN([Profit Ratio for Mass])

            returns the value for the Massachusset rows and null for the others.

           

          In the view both calculations will display the same value , but if you need it in the title you need to have the same value on every rows otherwise the title will display  "All",...  since it have more than one value to display (null and  the profit ratio).

           

          Hope I am clear enough.

          Michel

          1 of 1 people found this helpful
          • 2. Re: Text Summary Fields, LoD, and why does this work?
            Alex Kerin

            Ah, very good, thank you Michel - your explanation helped me understand the pitfalls - it was just by luck that MA and IN worked - a max for Colorado fails for example - it would need to be min and obviously that's not a solution. As I don't want to create a bunch of measures, I could combine your answers and use:

             

            "In CO, the profit ratio was "+ str({min(if [State]="Colorado" then [Profit Ratio per State]  end)})

            + ". In Indy, the profit ratio was "+ str({min(if [State]="Indiana" then [Profit Ratio per State]  end)})

             

            Many thanks.