Show aggr result, straight table expression

[Anonymous]

Hey friends

i have a problem showing the result of an aggr where a sales by item is null.

What im trying to do in the straight table, is to aggr the total sales value of an item category and get the ratio from a specific customer. This ratio is used then, to evaluate the potential by single item.

lets say the total item category is making 2200$ total and the selected customer is making 100$ of this category, i will get a ratio of 6.5% which can applied to the single items to evaluate effective sales against potential.

So now my problem is that the aggr function is showing null, on Items with no sales and im not able to calculate the potential.

Attached i have added a sample qvd, where i have taken apart, the steps to get to the result.

I think also here the solution is pretty easy, but i did not find a solution 

Would be great if somebody could give some advice on this

many thansk

[sunny_talwar]

This?

Expression:

Only({1}Aggr(NODISTINCT Sum({<Brand, %Item>}QTY*Value), ItemCategory)) /

Only({1}Aggr(NODISTINCT Sum({<Customer, Brand, %Item>}QTY*Value), ItemCategory))

[Anonymous]

based on the sample i send, yes, but there are many item categories which should not be evaluated.

lets say i have item category CAT3 to 99 where the customer is not making any turnover... there the valu null woul be ok, otherwise im going to display a item range of 50000 items, where just 200 are of interest.

Any possiblity to restrict it, without making the chart global to {1}?

[sunny_talwar]

Have you tested this out? From what I understand, the above expression should work for you, but if it doesn't can you provide a sample where it isn't working the way you would want?

[Anonymous]

yes, it work but it shows all  categories, also the one which are not needed...

I have addedsome items with CAT3

The CAT3 should not be displayed as the selected customer, is not purchasing items from cat3.

Many thanks again sunny

[sunny_talwar]

How about now

[Anonymous · Accepted solution]

With your input i got finally the whished result, just not sure whether is the correct way

Sample bellow: Customer2 ist buying 4 Items from CAT1 and CAT2, there is a potential assorment in these CAT1 and CAT2 which he could buy....

Expression to reduce data i used;

if(Only({1} Aggr(NODISTINCT Sum({< Brand, %Item>}QTY*Value), ItemCategory)) > 0,

Only({1} Aggr(NODISTINCT Sum({<Customer, Brand, %Item>}QTY*Value), ItemCategory)))

What you thing about it?

[sunny_talwar]

Well you know your requirement better than me. If this is what is giving you the right numbers, then I say this is the answer