topic Re: % Purchased in QlikView

% Purchased

dmac1971 — Thu, 21 Nov 2013 15:06:38 GMT

Is it possible for me to count the number of items purchased from Parts List A from 2013 sales? Or alternately show a full list of the items on Parts List A and a total of the sales for the year?

Re: % Purchased

Thu, 21 Nov 2013 15:08:04 GMT

Definitely. But you gotta give us more info than that! Do you have a sample file?

Re: % Purchased

DavidFoster1 — Thu, 21 Nov 2013 15:08:43 GMT

You need to give us a bit more than that...

Can you show us your data model?

Re: % Purchased

Thu, 21 Nov 2013 15:14:03 GMT

You'll have to replace the dummy fields for your own but for the count use a formula such as;

count( {< Year = {'2013'}, Partlist = {'A'} >} items)

Alternatively, to show the total sales for Parts List A, use a Straight or Pivot table with Items in your dimensions and use the expression;

sum( {< Year = {'2013'}, Partlist = {'A'} >} SalesValue)

You could remove the Year from the above formula and add that into your dimensions. You could also change the year to run off a variable which defaults to the current year.

But as mentioned above, without further information it is difficult to guide you.

Regards

Steve

Re: % Purchased

dmac1971 — Thu, 21 Nov 2013 15:14:43 GMT

Ok Lets say I have a list of items I want my shops to carry:

[Rec Sales]

Item No	Desc	Rec Qty
1234	Mug	5
1235	Plate	5
1000	Knife	10
1001	Spoon	20
1002	Knife	10

And I have their Sales Data :

[Actual Sales]

Shop	Item No	Desc	# Purchased
A	1234	Mug	15
B	1000	Knife	3
C	1234	Mug	12
D	1001	Spoon	4
E	1000	Knife	10

Now I want to do two things, I want to show [Rec Sales] with a total showing actual sales at the end against recommended sales, and then I want to show the actual % of items from the recommended list actually purchased? Hope this makes it clearer?

Re: % Purchased

DavidFoster1 — Thu, 21 Nov 2013 15:23:32 GMT

This is effectively a sales versus target issue.

The expression:

sum(# Purchased) / sum(Rec Qty) would give you the proportion of actual sales to recommended sales.

Re: % Purchased

dmac1971 — Thu, 21 Nov 2013 15:23:33 GMT

Sorry this is what I am aiming for below :

Item No	Desc	Req Qty	Act Qty
1234	Mug	5	27
1235	Plate	5
1000	Knife	10	13
1001	Spoon	20	4
1002	Knife	10
So 60% of Rec Items Purchased
However only 20% actually Purchased the right amount

Re: % Purchased

DavidFoster1 — Thu, 21 Nov 2013 15:32:14 GMT

🙂 d'oh I got the wrong end of that stick :-).

I would do this in the load script left joining the sales to the recommendation script and loading a 1 or 0 in a field called something like 'isrecommmended' and then use a preceding load to compare the sales quantity to the recommended quantity and returning a 1 or 0 in a 'matches recommendation' field. Then the front-end work is a sum of those flag fields compared to the count of all transactions.