5 Replies Latest reply: May 30, 2017 5:55 AM by scotly victor RSS

    How to calculate a dimension

    Liv ma

      Hello,

      I have a data set that looks like this

       

      Country          Product      Sales

      A                     X               1000

      A                     Y               100    

      B                      X               200

      B                      Z               2000

       

      I want to create a dimension which displays which product is the most sold per country

       

      the result would be: product X is the most sold for country A and product Z for country B

       

      it needs to be a dimension, not a measure, ideally I would do this in data loading the script

       

      thank you

      LM

        • Re: How to calculate a dimension
          Manish Kachhia
          Data:
          Load * Inline
          [
          Country,Product,Sales
          A, X, 1000
          A, Y, 100    
          B, X, 200
          B, Z, 2000
          ];
          
          
          Temp:
          Load Country, Product, SUM(Sales) as TotalSales Resident Data Group By Country, Product; 
          Left Join (Temp)
          Load Country, Max(TotalSales) as MaxTotalSales Resident Temp Group By Country;
          
          
          New:
          Load Country, Product, 1 as MaxSalesFlag Resident Temp Where MaxTotalSales = TotalSales;
          
          
          Drop Table Temp;
          
          • Re: How to calculate a dimension
            Andrea Gigliotti

            try this:

             

            a:

            load

                 Country,

                 Sum(Sales) as SalesMax

            resident your_table

            group by Country;

            left join

            load

                 Country,

                 Sales as SalesMax

                 Product

            resident your_table;




            • Re: How to calculate a dimension
              Michele De Nardi

              Main_tmp:

              LOAD

                  Country,

                  Product,

                  Sales

              FROM [Cartel1.xlsx] (ooxml, embedded labels, table is Foglio1);

               

               

              left join(Main_tmp)

              Load

                Country,

                max(Sales) as BestSales

              resident Main_tmp

                group by Country;

                 

              Main:

              NoConcatenate

              Load

                  if(Sales=BestSales,1,0) as [Flag Most Sold],

                  Country,

                  Product,

                  Sales

              Resident Main_tmp;

              drop Table Main_tmp;

               

              2017-05-30 11_12_39-Qlik Sense Desktop.png

              • Re: How to calculate a dimension
                Liv ma

                thank you, but this is too complicated for me and also of course my data structure is much more complicated than in the initial post, for example I have the same table on a monthly basis, so the question is actually "what is the most sold product per country in a certain month etc"

                 

                is there a way to calculate this in a visualization then, as a measure perhaps?

                 

                I was thinking using FirstSortedValue or something? I tried but it does not work, I get some "-" results for some records

                 

                thank you

                  • Re: How to calculate a dimension
                    scotly victor

                    Hi Liv ma ,

                     

                    Table1:

                    load * inline [

                    Country  ,        Product ,     Sales

                    A      ,               X    ,           1000

                    A    ,                 Y    ,           100   

                    B    ,                  X   ,            200

                    B     ,                 Z      ,         2000

                    ];

                     

                     

                    FinalTable:

                    load Country,FirstSortedValue(Product,-Sales) as MaxSoldProduct resident  Table1 group by Country;

                     

                     

                    drop table Table1;

                     

                    You can use MaxSoldProduct as Dimension