21 Replies Latest reply: Sep 7, 2011 7:46 PM by Stefan Wühl RSS

    Same Store Sale Indicator

    Camilo Lund

      Hi, I´m trying to create a SSS indicator (on a pivot table) to compare sales 364 days after the selected date, something like:

       

      sum sales if 364 day ago Sales<>0

       

      How can I create this value? please any help is more than welcome.

       

      Thanks,

       

      Camilo

        • Same Store Sale Indicator
          Stefan Wühl

          Camilo,

           

          I think your problem is similar to the problem I just worked on here:

          http://community.qlik.com/thread/34402?tstart=0

           

          You probably want to limit your sums to shorter period of times, you mentioned day period, so the set analysis looks maybe like

          {<Date = {"=$(=max(Date))"} >}

          resp

          {<Date = {"=$(=max(Date)-364)"}>}

           

          And you have to adapt the check for Qty > 0 according to your sum of Sales requirement, but that should be easy.

           

          Hope this helps,

          Stefan

            • Same Store Sale Indicator
              Camilo Lund

              Hi Stefan,

               

              I´m trying to use your aproach but probably I´m not using it properly, this is the code:

               

              =if(sum({<Fecha={"$(=(Fecha)-364)"}>}Venta6.0) >0, sum(Venta6.0))

               

              What I´m trying to do is to go 364 day before each selected date and see if sales were bigger than zero if that the case sum the selected dates sales (not including sales of the selected dates wich didn´t had any sales 364 days before).

               

              Please if I´m not being clear enough tell me.

               

              Thanks!

               

              Camilo

               

              Venta6.0 = Sales

              Fecha= Date

                • Same Store Sale Indicator
                  Stefan Wühl

                  Fecha is a field, so $(=(Fecha)-364) is not giving a unique answer (unless you selected exact one value from field). Also I think there should be an equal sign before the dollar sign.

                  try

                  =if(sum({<Fecha={"=$(=max(Fecha)-364)"}>}Venta6.0) >0, sum(Venta6.0))

                   

                  to limit the date. And you may also need to clear any other date field in the set expression, like Year, Month:

                  =if(sum({<Fecha={"=$(=max(Fecha)-364)"}, Mes= >}Venta6.0) >0, sum(Venta6.0))

                   

                  Maybe it's possible to use the set identifier 1 instead of $, this will disregard your current selections:

                  =if(sum({1<Fecha={"=$(=max(Fecha)-364)"}>}Venta6.0) >0, sum(Venta6.0))

                   

                   

                  Regards,

                  Stefan

              • Re: Same Store Sale Indicator
                Chris Cammers

                This is very interesting but "Same Store Sales" is a very fundamental measure in the retail space if it were me I would not mess around trying to calculate it in a chart. If you put a Same store sales flag in your fact table then you could multiply the sales amounts by the flag and get the appropriate total.

                 

                So I assume on your fact table you have store and data of sale so if you add the store open date you can then  evaluate the difference between the Date of sale and the Open Date and set the flag based on whatever threshold is appropriate.

                  • Re: Same Store Sale Indicator
                    Stefan Wühl

                    Hi Chris and Camilo,

                     

                    if I wouldn't trust that the calculations in the chart are as accurate as in the script in principle, I think I could stop working with QlikView. Currently we are just throwing in some ideas how to get the things done, I believe nobody wants to get into production without a thorough V&V.

                     

                    I also prefer a more simple solution over a more complex one, so if SSS is all about the store opening date, Chris' suggestion using just the opening (or first selling date) is probably a good idea. I was assuming that we need to take the Sales comparision with 52 weeks back as given requirement.

                     

                    Camilo, if you still need the comparison with the sales amount 52 weeks back for any given date, I worked out a solution, please find attached.

                     

                    I corrected the expression (Look at the expression in the text box, I changed the search expression to a literal, not sure yet why the first has not worked.

                    So, if you select a single date in the bottom left, you should get the correct result in the text box next to it (for a single selected date).

                     

                    But I assume you need a date range (like in a chart or table). I think that is problematic with the current solution, because the date as dimension conflicts with the date used in the set expression.

                    To work around this, I created a separate date field in the script, just as timeline for the tabe/graph. Please look at the second tab in the attached application.

                    Having the separate time line, you can use simple if-statements to check for past sales and filter the data accordingly.

                     

                    Regards,

                    Stefan

                    • Re: Same Store Sale Indicator
                      Camilo Lund

                      Yes Chris I already did what you say using the open date and using something like (Open Date <= Date-364) but at least here SSS is based on a day to day basis, If I just use the open date I will be taking into account those days that didn´t had any sales last year.

                    • Re: Same Store Sale Indicator
                      Camilo Lund

                      It does work on the example!! but it seems that a triple if is too much for my qvw it doesn´t show any data just a red cross on the pivot table

                        • Re: Same Store Sale Indicator
                          Stefan Wühl

                          That's too bad

                           

                          Well, if the 52 weeks are a fixed parameter, I guess we could really use a flag in the script.

                           

                          It would be a bit more complicated than a simple StoreOpenDate, we have to calculate the Sales sums per Store and Date, check for any given Date the sums 52 weeks back and there we go, maybe like this:

                           

                          SSSSales:

                          LOAD Date as Date,

                          Local,

                          Local &'-'& num(Date) as LocalDate,

                          sum(Sales) as SumSales

                          resident Input group by Local, Date;

                           

                          SSSDates:

                          left join (Input) Load Date, Local,

                          if (Lookup('SumSales','LocalDate',Local&'-'&num(Date-364),'SSSSales')>0, 1) as SSS

                          Resident SSSSales;

                           

                          drop table SSSSales;

                           

                          Please have a look at attached sample, I added a second store too ensure we got the SSS correct per Store (look at the demo date in load script, only 31.8./1.9.2010 differing).

                           

                          Hope this helps,

                          Stefan

                            • Re: Same Store Sale Indicator
                              Camilo Lund

                              It worked perfectly!!!!

                              The loading time went from 2:30 to 7:30 but then I can use swiftly the qv model

                               

                              Thanks Again Stefan!!!

                               

                              Camilo

                              • Re: Same Store Sale Indicator
                                Camilo Lund

                                Stefan,

                                 

                                It is posible to add a condition to restrict the SSS so it doesn´t include last year sales if this year sale were equal 0.

                                 

                                Something like:

                                 

                                SSSSales:

                                LOAD Fecha as Fecha,

                                ID,

                                ID &'-'& num(Fecha) as LocalDate,

                                sum(Venta6.0) as SumSales

                                resident Stage1.2 group by ID, Fecha;

                                 

                                SSSDates:

                                left join (Stage1.2) Load Fecha, ID,

                                if ((Lookup('SumSales','LocalDate',ID&'-'&num(Fecha-364),'SSSSales')>0) and (ID&num(Fecha), 'SumSales')>0 , 1) as SSS

                                Resident SSSSales;

                                 

                                drop table SSSSales;

                                  • Re: Same Store Sale Indicator
                                    Stefan Wühl

                                    Hi Camilo.

                                     

                                    That's quite simple, since in SSSDates, we are already having the data you need at hand, just use 'and SumSales>0':

                                     

                                    SSSDates:

                                    left join (Input) Load Date, Local,

                                    if (Lookup('SumSales','LocalDate',Local&'-'&num(Date-364),'SSSSales')>0 and SumSales >0, 1) as SSS

                                    Resident SSSSales;

                                     

                                    drop table SSSSales;

                                     

                                     

                                    But in your table, if SumSales =0, it will be removed anyway from the table, won't it? Or do you need a count of SSS?

                                    Or have I missed something in your question?

                                      • Re: Same Store Sale Indicator
                                        Camilo Lund

                                        I don´t need a count of SSS.

                                         

                                        I tryed using: if (Lookup('SumSales','LocalDate',Local&'-'&num(Date-364),'SSSSales')>0 and SumSales >0, 1) as SSS

                                         

                                        But I don´t see any diference on the Sales totals and I´m sure that some days this year didn´t had any sales.

                                        Maybe SumSales>0 isn´t differentiating by Local?

                                          • Same Store Sale Indicator
                                            Stefan Wühl

                                            It seems that I don't understand your requirement...

                                             

                                            If I use above line in the load and set a Sales on an abitrary date to zero, this date will be SSS flagged '0'. Same with the dates with SumSales = 0 364 days before.

                                             

                                            But anyway (regardless if SSS is set to 1 or 0 on these days), if the sum of Sales on these dates is zero, how should they influence the Sales totals?

                                             

                                            Could you give me an example, maybe together with an update of the inline load of my last script?

                                             

                                            Regards,

                                            Stefan

                                              • Re: Same Store Sale Indicator
                                                Camilo Lund

                                                Hmm I´m puzzled right now, I just try what I was talking about on your example and it does work without any problems!!

                                                 

                                                A 0 sales today influences the SSS in that I wont consider the sales 364 days before for that store.

                                                 

                                                I´ll try to figure out what is going on to ask with more details.

                                                 

                                                Thanks Stefan!

                                                 

                                                Camilo

                                                  • Re: Same Store Sale Indicator
                                                    Camilo Lund

                                                    I´m trying to get a better understanding of how this work doing somehing similar to another model wich has only years and months (in number format and separate fields). I´m trying to create a sss, but y got a script error sayin that it has an Invalid expression.

                                                    Do you know what could be wrong?

                                                     

                                                    SSSSales:

                                                    LOAD AñoMes as AñoMes,

                                                    Año,

                                                    Mes,

                                                    Local,

                                                    Local &'-'& num(AñoMes) as LocalDate,

                                                    sum(Real) as SumSales

                                                    resident Data group by Local, AñoMes;

                                                     

                                                    SSSDates:

                                                    left join (Data) Load AñoMes, Local,

                                                    if (Lookup('SumSales','LocalDate',Local&'-'&(Año-1)&'-'&(Mes),'SSSSales')>0, 1) as SSS

                                                    Resident SSSSales;

                                                     

                                                    drop table SSSSales;

                                                     

                                                    ------------------------------------

                                                    PS.

                                                    Año=Year

                                                    Mes=Month

                                                    Año &'-'& Mes as AñoMes,

                                                     

                                                    Thanks,

                                                     

                                                    Camilo

                                                      • Re: Same Store Sale Indicator
                                                        Stefan Wühl

                                                        Yes,

                                                         

                                                        if you use group by, you can only use fields stated in the group by clause as stand alone fields in the load, so in your example, Ano and Mes are not allowed standalone. You could use them in an aggregation function, though.

                                                         

                                                        maybe add Ano and Mes to your group by clause?

                                                          • Re: Same Store Sale Indicator
                                                            Camilo Lund

                                                            Mmm I added Mes and Año to de group clause but nothing happen, if I dont have a Date but Year (2008, 2009, 2010... etc) and month (1, 2, 3... 12) only, how should I write the script?  I´m trying something like:

                                                             

                                                            SSSSales:

                                                            LOAD AñoMes,

                                                            Año,

                                                            Mes,

                                                            Local,

                                                            Local &'-'& num(AñoMes) as LocalDate,

                                                            sum(Real) as SumSales

                                                            resident Data group by Local, AñoMes, Año, Mes;

                                                             

                                                            SSSDates:

                                                            left join (Data) Load AñoMes, Local, Año, Mes,

                                                            if (Lookup('SumSales','LocalDate',Local&'-'&num(Año-1) &'-'& num(Mes),'SSSSales')>0, 1) as SSS

                                                            Resident SSSSales;

                                                             

                                                            drop table SSSSales;

                                                              • Same Store Sale Indicator
                                                                Stefan Wühl

                                                                What do you mean with nothing happen? Do you still get a script error? Can you tell at which part line (e.g. by commenting some parts out?

                                                                 

                                                                Then, check if your SSSSales table is built correctly (by commenting out the SSSDates table load and the drop table). One step after the other.

                                                                 

                                                                Then, check that your format of AnoMes used for LocalDate in SSSSales and  the lookup in SSSDates are identical, e.g. by putting

                                                                Local&'-'&num(Año-1) &'-'& num(Mes) as LocalDateprev

                                                                 

                                                                in the SSSSales table, just for inspection.