5 Replies Latest reply: Mar 1, 2013 3:17 PM by Kevin Flynn RSS

    Classifying by size expression

      Hello,

      Personnel how do I sort by selling a dimension?

       

       

      dimensions: Store, Vendor, customer code and customer name

       

       

      expressions: Rank and sale

       

       

      In dimension seller have a condition, just shows sellers that sell on the selected date.

       

       

      The size is what I classify the customer name. Have you tried to sort by the expression size and customer name can not.

       

       

      Can anyone help me?

       

       

      Thank you.

        • Re: Classifying by size expression

          Your issue is with your dimensions. If Customer Name is your lowest dimension, sorting by the highest sale will sort your sales from highest to lowest inside of Customer Name. You might want to try making customer code and customer name expressions instead of dimensions.

            • Re: Classifying by size expression

              Other_peoples_shoes,

               

              Below the print and the codes used to mount the screen.

               

               

              The red marking is a classification of the sale value (right column).

               

               

              Can you help me?

               

               

              Thank you.

               

              tela.jpg

              DIMENSIONS:

              * Store,

               

              * IF(MAKEDATE(ANO_VENDA,MES_VENDA,DIA_VENDA) >= DATE(Ano1) AND MAKEDATE(ANO_VENDA,MES_VENDA,DIA_VENDA) <= DATE(Ano2),NOME_VENDEDOR),

               

              * Cliente code,

               

              * Cliente

               

              EXPRESSIONS:

               

              Column Rank:

              RANK(SUM(IF(

              MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA >= DATE(YEAR1)

              AND

              MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) <= DATE(YEAR2),SALES)),1,1)

               

              Column Sales:

              SUM(IF(MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) >= DATE(YEAR1)

              AND

              MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) <= DATE(YEAR2),SALES)

               

              Column number of pieces:

              SUM(IF(MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) >= DATE(YEAR1)

              AND

              MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) <= DATE(YEAR2),PIECES)

               

              Column amount sales:

              COUNT(DISTINCT IF(MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) >= DATE(YEAR1)

              AND

              MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) <= DATE(YEAR2),TICKET)

               

               

              SORT EXPRESSIONS:

               

              SUM(TOTAL<NOME_VENDEDOR>IF(

              MAKEDATE(ANO_VENDA,MES_VENDA,DIA_VENDA) >= DATE(Ano1)

              AND

              MAKEDATE(ANO_VENDA,MES_VENDA,DIA_VENDA) <= DATE(Ano2),SALES))


                • Re: Classifying by size expression

                  2. MakeDate. Telling QlikView to look up 3 different columns and compute a function every time that you try to do everything will make this application work very, very slowly. If you are loading from raw data, use

                   

                  load *,

                  makedate(year_venda,month_venda,day_venda) as venda_date in your load script to calculate the dates once, when you load the data, and store it thereafter. If you are loading from .qvds, go back to edit the file that creates the qvd.

                   

                  Trust me, your program will work much better.

                  • Re: Classifying by size expression

                    3. If statements.

                     

                    If your program is still running too slowly, consider using set expressions instead of if statements for your calculations. If statements are easier to program, but harder for QV to calculate.

                     

                    I think that the equation you want to use is: sum({$<venda_date={>year1<year2}>}sales), though I'm not 100% certain. It should keep things running much more smoothly.

                • Re: Classifying by size expression

                  Ok, you have several problems.

                   

                  1. Dimensions.

                   

                  The way that QlikView sorts things is very strange, and so it behaves differently then you would expect.

                   

                  Let me give an example:

                   

                  If D_1 is a dimension, and you tell QlikView that you want to sort this table by Rank:

                  D_1  Rank V_1

                  A        1     apple

                  A        3     pear

                  B        4     banana

                  B        2     grape

                   

                  You will not get Rank = 1 2 3 4. Sort functions inside of your dimensions. You will get:

                  D_1  Rank V_1

                  A        1     apple

                  A        3     pear

                  B        2     grape

                  B        4     banana

                   

                  as QV will sort each Dimension by rank.

                   

                  This table, on the other hand, will not sort at all:

                  D_1 D_2 Rank V_1

                  A     A     1     apple

                  A     B     3     pear

                  B     A     4     banana

                  B     B     2     grape

                   

                  So in order to get any sorting done, you will have to remove some dimensions. Consider making Client Code an expression, rather than a dimension.