7 Replies Latest reply: Jul 18, 2018 1:07 PM by Lucas Machado RSS

    Average If -- Item to Category

    Lucas Machado

      Hey guys! I'm new to Qlik Sense and I'm trying to perform an analysis but can't figure it out.

      I have a huge database and I'm trying to calculate the average/sum values for some purchases cointaining certain Items. This gets tricky in excel since I have lots of data and I would have to perform some vlookups and merge some tables together. I understand Qlik Sense can do it faster, especially since my server and database is already running on Qlik.


      So here is the exemple:


      I have a table containing (among other fields) a purchase code, the category, the item bought and it's quantity and "price".

      What defines this table is the item, and since a purchase may cointain several itens, the purchase and catecory fields also appear several times. I want to work with the purchases (circled in green) that cointains a certain item (in yellow) and see how much revenue and volume, for exemple, a purchase (A, B, D and F, in this case) have generated. So an item should "carry information "behind it", regarding the entire purchase. Eg.: 2222 should tell me about all purchases made in A, B, D and F. And that I don't know how to do!


      Like I said, in Excel this is kinda tricky and with millions of lines, the analysis gets really slow and "annoying" to perform.


      I have studied Sense a bit, I have seen some videos regarding the aggr function and I think it would solve my problem, bur I couldn't quite figure it out yet.


      Could you guys help me with how to formulate the function that would solve this?


      Thanks a lot!

        • Re: Average If -- Item to Category
          Olivier GAUTIER



          try this,


          in first dimension :

          if(aggr(count({<item = {'222'}>} item),purchase)>0,purchase)

          and check 'supress if null'


          you'll have all ticket with at least one item with all items of ticket (item in second dimension)



            • Re: Average If -- Item to Category
              Lucas Machado

              Hey Olivier, thanks a lot for your help.


              Like I said, I'm very new to Sense, so my questions might be very basic.


              I understand the logic of what you have proposed. My question now is, how do I go from this (defining the dimension) to calculating the sum of the revenue generated, the numbe of purchases etc. In the eg given it would be:


                            Defining the dimension (green)                        results obtained using this dimension

                                            Step 1                                                                  Step 2


              How do I go from "step 1" to "step 2"?


              Thanks again!