3 Replies Latest reply: Apr 17, 2015 4:59 AM by Simon Hogg RSS

    Better to create derived Measures during Load, or after Load (in Sense App?)

    Simon Hogg

      (With thanks to Michael Tarallo for correcting my posting)

       

      My data Structure is like this;

       

      CompanySales
      Walmart1,400,000
      Target1,600,000

       

      But I want to create two measures; "Walmart Sales" and "Target Sales".

       

      I can create two derived Metrics as 'Master Items';

           SUM (If (Company = "Walmart" , "Sales")) to give Walmart Sales

           SUM (If (Company = "Target" , "Sales")) to give Target Sales

       

      An I can do this inside my QlikSense app.  But is this the best place to do it, or should I be doing it within by data load script?  Is there any difference to the usability (apart from them being created as 'Master Items')?

       

      Thanks,

        • Re: Better to create derived Measures during Load, or after Load (in Sense App?)
          Jonathan Poole

          In the load script you would have 2 options:

           

          Use IF statements to extend the table to something like this.

           

          -> the main issue with this is that a user would STILL need to add sum(Walmart Sales) or avg(Walmart Sales) to see the sums in the UI. This is needed to see the summarized totals for anything but the granular data

           

           

          CompanySalesWalmart Sales

          Target Sales

          Walmart140000014000000
          Target160000001600000

           

          The other way to do this in the load script is to use a variable:

           

          set vWalmartSales='sum( if(Company='Walmart',Sales)) ';

          you could also do this which i would recommend. Its an alternative syntax that uses SET ANALYSIS which can be faster

          set vWalmartSales='sum( {<Company={Walmart}>} Sales) '

           

          In this scenario the 2nd is pretty useful because you can use it in ANY place expressions are allowed... 'color by expression' , 'measure expression', 'dimension expresison'  even in a master item.

           

          If you do it as a Master Item, the key benefit is that your users can quickly drag and drop it to create new visualizations using the same master expression. Thats a good thing. Only issue with this (and only reason why you may not use this) is that master items can't be referenced by OTHER expressions just yet. They will soon but not right now. Variables can.

           

          So variables may be the most flexible because you can use them everywhere, but now or perhaps soon doing it in one place (Master Items) may feel cleaner and allow administrators to rely less on script.

           

          hope it helps

          • Re: Better to create derived Measures during Load, or after Load (in Sense App?)
            Simon Hogg

            Thanks - I'll give it a try in the load script.