% Purchased

[dmac1971]

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?

[#2]

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

[DavidFoster1]

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

Can you show us your data model?

[#4]

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

[dmac1971]

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?

[DavidFoster1]

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.

[dmac1971]

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

[DavidFoster1]

🙂 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.