8 Replies Latest reply: Feb 17, 2014 8:41 AM by Murti Akkime RSS

    Like for Like

      Is there an easy way to exclude values from an expression that don't appear in all returned values of the dimension. ie i would like to be able to only return Like for Like values.

       

      eg I want to be able to compare the footfall of our shopping centers on a like for like basis.

       

      I have already read the discussion http://community.qlik.com/thread/1342 but this did not work for my circumstances as want to be able to change selections and circle dimensions.

       

      Many thanks,

       

       

      Lawrence

        • Like for Like
          Stephen Redmond

          Hi,

           

          You appear to have adequately explained what you don't want, but not exactly what you do want.  Perhaps you could post an example QVW showing what you are looking for?

           

          I am guessing that a P() set might do the trick, but I can't give you a good response without knowing what you need.

           

           

          Regards,

           

           

          Stephen

            • Re: Like for Like

              Stephen,

              Thanks for responding, I have attached the QVW file I am working on. I deally I am looking to have a flag or button where I can switch from all results to just like for like results.

               

              in the example below I would like to exclude sites that don't appear in all four years, as this screws the results and therefore need a like for like comparison.

               

              footfall screen grab.jpg

              Many thanks,

               

              Lawrence

                • Re: Like for Like
                  Stephen Redmond

                  Hi Lawrence,

                   

                  Bit of a challenge there - thanks!  In fact, I have just blogged on this subject: http://qliktips.blogspot.com/2011/06/and-mode-in-set-analysis.html

                   

                  You want to see only sites that have footfall in each year.  The default search (and Set) in QlikView is "OR" whereas you want an "AND".

                   

                  So, in your script, you will need to load a separate Footfall Year field:

                   

                  FF_Year:

                  Load Distinct

                            SiteName,

                            Year(FootfallDate) As FootfallYear

                  Resident Footfall;

                   

                  Add the new field to the layout (you can remove it later if you want but it might be handy) and set the "AND-mode" option in the properties.  You might need to reload at this stage.

                   

                  Then you can use this expression in your chart:

                   

                  sum({<FootfallYear=P({$} Year)>} [In Count])

                   

                  Hopefully this gets you along further.

                   

                   

                  Regards,

                   

                   

                  Stephen

                    • Like for Like

                      Hi Stephen,

                      Thanks for this and you blog posting, that commuicated my problem it more clarity than I could have managed. Your solution work a treatt, however is there an easy way to switch the selection between AND and OR.

                       

                      Lawrence

                        • Like for Like
                          Stephen Redmond

                          Hi Lawrence,

                           

                          The easiest way is to have 2 fields - one for AND and one for OR.

                           

                          You can then change your expressions and show/hide fields based on whatever way you want to switch them (button, etc.)

                           

                           

                           

                          Stephen

                            • Like for Like

                              Hi Lawrence & Stephen,

                               

                              I'm still on PE, so can't open you qvw, but think there may be an easier solution like:

                               

                              Sum( {$<SiteName = { "=(count( DISTINCT Year(FootballDate) )=4)" }>} [In Count])

                               

                              Tested it on the example from Stephen's blog

                               

                              Sales:
                              LOAD * INLINE [
                              Customer, Year, Sales
                              Customer A, 2009, 111
                              Customer B, 2009, 111
                              Customer C, 2009, 111
                              Customer A, 2010, 222
                              Customer B, 2010, 222
                              Customer C, 2010, 222
                              Customer A, 2011, 333
                              Customer C, 2011, 333
                              ];

                               

                              Sum( {$<Customer = { "=(count( Year )=3)" }>} Sales)

                               

                              That worked, so thinking might work for you too.

                               

                              hth Jeroen

                    • Re: Like for Like
                      Angelos Vlisidis

                      The solution is extremely simple. I use the function aggr that returns a set of rows for each combination of the fields.

                       

                      The case is the following:

                      A table with 4 fields: Year, Month, Shop, Sales, having data for 2010 and 2011.

                       

                      The Like for Like KPI for a shop should compare only the months having sales on both years.

                       

                      The job is done by a variable that returns only the records having sales on the same month:

                       

                      //Variable L4L_Sales(year1, year2): Returns the sales of Year1 if there are sales on the same month of year2.

                      SET L4L_Sales = aggr(if((sum({$<[Year]={$1}>}Sales) > 0) AND (sum({$<[Year]={$2}>}Sales) > 0), sum({$<[Year]={$1}>}Sales)), Shop, Month);

                       

                      In a grid chart then we can use the following expression:

                      L4L Percent: (sum($L4L_Sales(2011, 2010)) - sum($L4L_Sales(2010, 2011)))/sum($L4L_Sales(2010, 2011))

                       

                       

                      Check the attached qvw.

                        • Re: Like for Like

                          Hello Angelos,

                           

                          I like your approach it is extremely interesting.

                           

                          Do you have any example for me about L4L analyse;

                          I have four Table;

                          1- Clients Table

                          2- Store Table (including StoreCode, StoreName, GPS locations, Store opening Date and Closing date, Etc...)

                          3- Calender Table

                          4- Sales Transaction Table (including StoreCode, ClientCode, Date, Qty, SalesSum, InvoiceNo, InvoiceLineID, Etc.)

                           

                          I need a pivot chart total sales and L4L basis according to Store Opening dates. we will take MonthStart(OpeningDate)

                           

                          Could you please help and share any example. My data is nearly 2 M row. Thanks

                           

                          Kind regards

                          Murat