If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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!
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
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
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!
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
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!
thanks, go on with your ticket analysis.
next time purchases with two same items 😉 ?
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!
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!