Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm fairly new to Qlikview and I have a question.
I have orders which have an ORDER_ID and every order has articles (ART_NAME).
I have a listbox with ART_NAME, I would like to select an article and see in a straight table how frequently other articles show up in the same order as the first selected article.
I haven't gotten very far because I can't wrap my head around how the question should be formulated in qlikview but I know there's some set-analysis involved.
aggr(Count(Order_ID), ART_NAME) this gives me how many times each article appears on all orders.
Idea:
aggr({$<ART_NAME>} Count(Order_ID), ART_NAME)
It doesn't work and I didn't expect it to because it doesn't feel right, i just need help figuring it out.
Thanks in advance!
I think you can do it like I described here:
http://community.qlik.com/message/208686#208686
Regards,
Stefan
Script:
T:
LOAD
*,
date(date#(DATE_TXT,'D.M.YY'),'DD.MM.YYYY') as Date
;
LOAD * INLINE [
CID, PRODUCT, DATE_TXT
1, A, 1.1.12
1, B, 1.2.12
1, C, 1.3.12
2, A, 1.3.12
3, A, 1.2.12
3, B, 1.3.12
4, C, 1.1.12
4, B, 1.2.12
5, C, 1.2.12
6, B, 1.3.12
];
LOAD CID, PRODUCT as PDIM2 Resident T;
Then create a pivot table with dimensions PRODUCT and PDIM2 and as expression count(distinct CID)
Hi Stefan,
Thanks for your reply. It works but since there are 30000 orders and 3000 articles the crosstable becomes large. It has around 200000 rows and the count function takes its sweet time processing the list for every update and most cells are blank.
Cheers,
Marcelo
There are probably things to improve, e.g. I missed that you want to actively select a product and see the related products.
Try maybe replacing the resident LOAD in my above sample by
LOAD DISTINCT CID, PRODUCT as PDIM2, 1 as COUNT Resident T;
Then create a straight table with Dimension PDIM2 and as expression sum(COUNT)
You should be able to select a product in PRODUCT and see the results in the straight table now.
Hi Marcelo,
I suggest you to use And Mode in list Box- It will improvw your request.
This will give you be able to chosee which Products sold together and which Products not.
for example when choose together like this:
Its mean that you see all the sales with this two product.
If you fhold and press one selection it will turn to red and it will show all the sales with A and not B.
Look at the attace qvw,
Regards,
Yigal/