6 Replies Latest reply: Nov 26, 2013 10:00 AM by Tom Sidwell RSS

    Complicated Formula

      Hi,

       

      From the below table I need to know the number of customers that have...

       

      1.) Bought hats in F11 and F12 and haven't bought anything else

      2.) Bought Gloves in F11 and F12, but have also bought something other than Gloves in F12

      3.) Bought more than 1 type of product in both F11 and F12.

       

      On top of this I need to know for each question above how much the customers have spent on each item in each year!

       

      Anyone any idea how to tackle this problem?  Or is it beyond the scope of Qlikview?

       

      CustomerYearItemValue
      AF11Hat10
      DF11Hat10
      EF11Hat10
      BF11Glove5
      BF11Glove5
      CF11Glove5
      EF11Glove5
      AF11Hat10
      BF11Glove5
      CF11Glove5
      FF11Hat10
      FF11Glove5
      BF12Hat10
      BF12Hat10
      AF12Hat10
      BF12Glove5
      BF12Scarf15
      CF12Glove5
      EF12Glove5
      EF12Glove5
      DF12Hat10
      EF12Glove5
      CF12Glove5
      FF12Hat10
      FF12Glove5
      BF12Scarf15
      FF12Scarf15
        • Re: Complicated Formula
          Tresesco B

          It is very much possible in QV. I will give you a hint; try Set Analysis with P().

          • Re: Complicated Formula

            Hi Tom

             

            Do you want this adding to the table above?  I presume you will be creating a new table, what dimensions will you be using?

             


            Regards


            Steve

              • Re: Complicated Formula

                Hi Steve,

                 

                I need separate formulas as the client would like the answers in stand alone text box unfortunately rather than in a table with a dimension.  The table above is mock up of the data I would want to analysis.

                 

                An example of what one of the text boxes would look like is....

                 

                Previously purchased Hats only, but now buying other items

                1: Customer count

                2: Hat sales previous / now

                3: Non Hat sales previous / now


                Any help would be appreciated as it has got me stumped!

              • Re: Complicated Formula
                Sundarakumar Kalaimani

                Hi Tom,

                 

                Please find the attachment.

                I have done first two requirements.

                Hope this helps...

                 

                -Sundar

                  • Re: Complicated Formula

                    Hi Sundar,

                     

                    Thanks for this, it is probably going in the right direction,but doesn't quite get the answers I want.  I think it needs to involve Set Analysis, but I am not sure as the same customer has to have fulfilled several criteria before being counted.  For instance, "Previously purchased Hats only, but now buying other items", means the customer has to have bought Hats and nothing else in F11, and in F12 have not bought hats, but have bought other items.

                     

                    Thanks for trying.

                  • Re: Complicated Formula

                    Hi ALL

                     

                    Here is the SOLUTION. See attached file.

                     

                    Question #1 resolution:

                    <Pivot Table>

                    Dimension: Customer

                    Expression:

                    =Sum({<[year]={F11,F12},item={Hat}>}[value]) > 0 And

                    Sum({<[year]={F11,F12},item={"*"}-{Hat}>}[value]) = 0

                     

                     

                     

                    Question #2 resolution:

                    <Pivot Table>

                    Dimension: Customer

                    Expression:

                    =Sum({<[year]={F11,F12},item={Glove}>}[value]) > 0 And

                    Sum({<[year]={F12},item={"*"}-{Glove}>}[value]) > 0

                     

                     

                    Question #3 resolution:

                    <Pivot Table>

                    Dimension: Customer

                    Expression 1:

                    =if( Aggr( Count({<year={F11,F12}>} DISTINCT [item]), [customer] ) > 1, 'YES', 'no (only one)')

                    Expression 2:

                    =Aggr( Count({<year={F11,F12}>} DISTINCT [item]), [customer] )

                     

                     

                     

                    Best Regards

                     

                    Evandro Segura

                    Brazil