13 Replies Latest reply: Feb 20, 2015 12:57 PM by Tang man RSS

    Price Difference Table chart in Qlik Sense

    Tang man

      Dear Community

       

      I wonder how can I build a Price Difference Table in Qlik Sense. It is to show the Percentage price variance between Current price and Last price for parts.


      Calculation : [ ( Current price - Last price ) / Last PO price x 100% ]

       

      Part No.PriceDatePrice Difference
      ABC123410001/1/2015-
      ABC123415001/2/201550%
      ABC345615003/1/2015-
      ABC345613204/2/2015-12%
      BCD3455300016/1/2015-
      BCD3455300015/2/20150%
      ERT9999200020/2/2015-
      JSH767010005/1/2015-
      JSH767011002/2/201510%
      RTY8909300021/1/2015-
      RTY89093000.154/2/20150%
      TYU2435235013/1/12015-
      TYU2435250020/1/20156%
      TYU2435300015/2/201520%
      UIO898923504/1/2015-

       

      How can I set the expression in Measures for calculating Price Difference?

       

      Thank you very much. Please give me a helping hand.

        • Re: Price Difference Table chart in Qlik Sense
          Michael Tarallo

          Hello Tang man,

           

          In the Master Items panel - you can create a new measure as such:

           

          You would calculate quite simply such as:

           

          sum( [Current price] - [Last price] ) / [Last PO price] x 1 - the format the result in the column.


          Here is an example using sales and cost to create margin percentage:


          Sum([Sales Margin Amount])/Sum([Sales Amount])


           

          I also attached the sample App - the last sheet named Margin has this example:

           

          Please check out this video as well: Creating Master Items (video)

           

          Please mark the appropriate replies as correct / helpful so our team and other members know that your question(s) has been answered to your satisfaction.

           

          Regards,

          Mike Tarallo

          Qlik

            • Re: Price Difference Table chart in Qlik Sense
              Tang man

              Hello Michael Tarallo

              Thank you very much for help. But I still can't build the table. As using you concept, Price Difference should be calculated by


              ([Price] /  [Last Price]) -1 or

              (sum([Price]) /  sum([Last Price])) -1

                                                         

              Part No.DatePriceLast PricePrice DifferenceRemark
              ABC12341/1/20151000--Null Value
              ABC12341/2/20151500100050%
              ABC34563/1/20151500--Null Value
              ABC34564/2/201513201500-12%
              BCD345516/1/20153000--Null Value
              BCD345515/2/2015300030000%
              ERT999920/2/20152000--Null Value (new part in Feb)
              JSH76705/1/20151000--Null Value
              JSH76702/2/20151100100010%
              RTY890921/1/20153000--Null Value
              RTY89094/2/20153000.1530000%
              TYU243513/1/120152350--Null Value
              TYU243520/1/2015250023506%Different Last Price at different moment.
              TYU243515/2/20153000250020%Different Last Price at different moment.
              UIO89894/1/20152350--Null Value (no next value)


              However, I have some problems.

               

              1.  I don't know how to extract the Last Price for every part.

              a. If I using something like max(Date), it only returns one maximum date (20/2/2015)  for the parts (ERT9999, TYU2435), not every part's maximum date (Last date).

              b. Besides, in the case, the last price for Part No. TYU2435 at 20/1/2015 is 2350 and at 15/2/2015 is 2500. How can I find the right last price at the particular moment?

               

              2. I don't know how to change the data to be Null value when comparing the same date value.

              If there is comparing the same date value, it should be null value ( - ) to prevent misleading. How can I make it? It there using If statement or Null() function? As Min(Date) is not workable too.


              I got much problems in the table chart. Please help me and thank you very much for answering.

                • Re: Price Difference Table chart in Qlik Sense
                  Michael Tarallo

                  Ah - ok - form what I see - your "last price" is calculated from the "price field" and the date field - in other words you do not have an actual field names LAST PRICE - correct?

                   

                  let me take a look and I will attempted to build you a sample based on this.

                   

                  Mike

                  • Re: Price Difference Table chart in Qlik Sense
                    Michael Tarallo

                    Hi Tang man - check this out - attached .qvf file - copy to C:\Users\<user profile>\Documents\Qlik\Sense\Apps  and refresh the Qlik Sense Desktop by pressing F5

                     

                    Note: I am using the WEBFILE data connection in Qlik Sense to THIS actual discussion thread as a data source. YES - you can do that - it will automatically decipher the tables and display them as selectable data tables.

                     

                    I am using the PEEK function to look at the previous row of data - that will put the new data value on the current row to perform the calculation - as in:

                     

                    (Price - peek([Price])) / peek([Price]) * 1 as Difference

                     

                     

                    LOAD
                        "Part No.",
                        Date(Date#([Date],'DD/MM/YYYY'),'MM/DD/YYYY') as "Date",
                        peek([Price]) as PreviousPrice,
                        Price,
                        (Price - peek([Price])) / peek([Price]) * 1 as Difference
                    FROM [lib://715333]
                    (html, codepage is 1252, embedded labels, table is @2);
                    
                    

                     

                    Click to enlarge image:

                     

                    Let us know how you do.

                     

                    Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                     

                    Regards,

                    Mike Tarallo

                    Qlik

                • Re: Price Difference Table chart in Qlik Sense
                  Michael Tarallo

                  OK GOT IT - this time to look like your example - see attached:

                   

                  I also sorted the dates correct in the interface:

                   

                  Click to enlarge image

                   

                    if(peek([Part No.]) = [Part No.],

                     (Price - peek([Price])) / peek([Price]) * 1,0) as PeekDifference

                   

                   

                  LOAD
                      "Part No.",
                      Date(Date#([Date],'DD/MM/YYYY'),'MM/DD/YYYY') as "Date",
                      previous([Price]) as PreviousPrice,
                      Price,
                  
                    if(peek([Part No.]) = [Part No.],
                    (Price - peek([Price])) / peek([Price]) * 1,0) as PeekDifference
                  
                  FROM [lib://715333]
                  (html, codepage is 1252, embedded labels, table is @2);
                  
                  

                   

                   

                  Please mark the appropriate replies as helpful / correct so our team and other members know that your question(s) has been answered to your satisfaction.

                   

                  Regards,

                  Mike Tarallo

                  Qlik