6 Replies Latest reply: May 17, 2011 5:27 PM by jheersch RSS

    Tips to do a cross dimension calculation?

      Dear all,


      As newbie in Qlikview I am currently trying to found it's possibilities.


      I have the following data set:


      Measure     Year      Month     Store     Value

      Sales          2010     Jan          1          100

      Profit           2010     Jan          1          10

      Sales          2010     Feb          1          200

      Profit           2010     Feb          1          25


      I want to create pivot table, which next to sales, profit also contains profit % (= profit/sales) for the shown store/month. In other words to do a calculation across the dimension based on the shown selection in other dimensions.


      Is this possible in Qlikview?


      Really appreciate your help in this!





        • Re: Tips to do a cross dimension calculation?
          Chris Cammers

          If I were you I would not use this data set "as is." There are a couple of fundamental things you should do...


          First, I would split the "value" field into two fields based on your record type(sales and profit)


          So I would do something like this









          Value AS Sales

          From XYZ

          Where Measure = 'Sales'




          Then you are going to do a full outer join just in case you have any sores, months or years without sales(unlikely, but no system is perfect)


          Join (Table1)





          Value AS Profit

          From XYZ

          Where Measure = 'Profit'


          So now you end up with something like this...


               Year      Month     Store     Sales     Profit

               2010     Jan          1          100          10

               2010     Feb          1          200          25


          You should be able to do most of your calculations in your charts



          You could also do this without transforming your data using Set Analysis but I definitely would not reccomend that approach.        

          • Re: Tips to do a cross dimension calculation?
            Erich Shiino

            Hi, I'd recommend using set analysis.

            This syntax basically force a selection on the fields:

            For your data, it would be something like this.







            sum({<Measure={Profit}>}Value) / sum({<Measure={Sales}>}Value)



            You can also check the results on the attachment.

            Hope it helps,



            • Re: Tips to do a cross dimension calculation?



              Have look at the attached application.

              Hope this may help you.


              - Sridhar

                • Re: Tips to do a cross dimension calculation?
                  Chris Cammers

                  I'm  not saying that the set analysis approach will not work but I think you will be making some serious usability compromises in your data model if you don't transform the data. Any user who you share this document with will have to master Set analysis just to produce a simple sales chart. If you separate sales and profit into separate columns then the user only needs to sum the column they want.


                  Check out the attachment and you can decide what you want to do, each approach will work.

                    • Re: Tips to do a cross dimension calculation?

                      I like Chris's method and his justification is equally matched. Just one question Chris, why exactly do you on the join statement, list all fields again. Could you not just load the Profit part and join.


                      Would it not still match everything?



                      LOAD * INLINE [

                           Measure, Year, Month, Store, Value

                           Sales, 2010, Jan, 1, 100

                           Profit, 2010, Jan, 1, 10

                           Sales, 2010, Feb, 1, 200

                           Profit, 2010, Feb, 1, 25








                      Value as Sales

                      RESIDENT TempTable

                      WHERE Measure = 'Sales';


                      JOIN (FinalTable)


                      //   Month,

                      //   Year,

                      //   Store,

                      Value as Profit

                      RESIDENT TempTable

                      WHERE Measure = 'Profit';

                      DROP TABLE TempTable;