Average If -- Item to Category

[lucas_zec]

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!

[ogautier62]

Hi,

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)

regards

[lucas_zec]

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!

[ogautier62 · Accepted solution]

so first :

in first dimension :

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

and check 'supress if null'

for step2, expressions :

- nb purchases with '222' :     count(distinct purchase)

- total items :                       count(item)

- sum revenue :                    sum(revenue)

regards

[lucas_zec]

It took me a while, but now I understand!

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

then it goes

count (distinct X, purchase)

count (X, item)

sum (X, revenue)

right?

I thought after defining this X dimension, I had to go to a different "app" or something (I can't even properly explain what I had in mind, that's how new to this I am hehe). I have made it work though.

Thanks a lot!

olivier.gautier‌!

[ogautier62]

thanks, go on with your ticket analysis.

next time purchases with two same items 😉 ?

[lucas_zec]

I've been trying, but no luck lol

where do I add the same items part?

it is actually something I would have to add later anyways, since the analysis I'm doing is based on a "special item" and customers shouldn't be able to get more than one per purchase. Since I have already 300k sold items and only 270k purchases, I know for a fact that it has already happened.

Thanks again!

[lucas_zec]

Hey! I've been strugling witht that, could you please help me?

I've been trying with

if(aggr(NODISTINCT count({<item = {'222'}>} item),purchase)>1,purchase) -- to indicate more than one count?

But no luck!

Thanks again!