7 Replies Latest reply: Jun 16, 2016 6:15 AM by Stefan Wühl RSS

    Like for Like in set analyses?

      Dear QlikView fanatics,

       

      I have a quistion about indirect set analyses: I think it should be possible to do the folowing:

       

      Sum Turnover(#Netvalue_EUR)  of this year where store is open(storestatus: active selling point) this periode but also last period. I've tried to use the p() statement but i couldn't make it work.

       

      Does anyone has a clue to get it right?

       

       

       

      set analyses.PNG

       

       

        • Re: Like for Like in set analyses?

          herewith a code copiable

           

          sum({$<Year={$(=Only(Year))}> *

               $<StoreName = p({$<StoreStatus={'Active Selling Point'}>}  StoreName)>

                     + $<Year = p({$<Year={'2013'}>}  Year)>

                    + $<Year = p({$<Year={'2012'}>}  Year)>

          #NetValue_EUR)

            • Re: Like for Like in set analyses?
              Stefan Wühl

              I am not 100% sure what your requirement is:

               

              "Sum Turnover(#Netvalue_EUR)  of this year where store is open(storestatus: active selling point) this periode but also last period."

               

              Could you detail this a bit more (or explain it with some sample lines of data)?

               

              Maybe like

               

              sum(

              {$<

              Year={$(=Only(Year)),$(=Only(Year)-1)},

              StoreName = p({$<StoreStatus={'Active Selling Point'}>}  StoreName)

              >}

              #NetValue_EUR)

                • Re: Like for Like in set analyses?

                  Thanks for your quick reply. I'll try to explain in more detail:

                   

                  For example your expression:

                  sum(

                  {$<

                  Year={$(=Only(Year)),$(=Only(Year)-1)},

                  StoreName = p({$<StoreStatus={'Active Selling Point'}>}  StoreName)

                  >}

                  #NetValue_EUR)

                   

                  This would give me the turnover of selected year + lastyear where stores where open.

                   

                  But i would like to only sum current year turnover, alltough the store need to be open this year and last year. In the retail industry they call this Like for Like.

                   

                  Maybe current year and lastyear should be contained in the p() statement where openstores are associated?

                   

                  I hope its abit more clear this way.

              • Re: Like for Like in set analyses?
                Jozef Stofko

                Hello, I have problem with set analyse. I want to filter only values, where is the rows max. I have this, but it is not good.

                Sum ({<[Rank of line] = {"$(=max([Rank of line])"} >} [Sum of income]). Thanks for helping me.

                 

                Here is my table.

                ID Purchase          Row          Income  

                N01                      1                  200

                N02                      1                  50
                N02                      2                  70

                N02                      3                  60

                N03                      1                  10

                N03                      2                  35

                N04                      1                  10

                N04                      2                  40

                N04                      3                  60

                N04                      4                  90

                N05                     1                  20

                 

                Set Analayse should choose me only this table.

                ID Purchase          Row          Income  

                N01                      1                  200

                N02                      3                  60

                N03                      2                  35

                N04                      4                  90

                N05                      1                  20