topic Re: Show aggr result, straight table expression in QlikView

Show aggr result, straight table expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Show aggr result, straight table expression

sunny_talwar — Wed, 13 Jul 2016 15:59:00 GMT

This?

Expression:

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

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

Re: Show aggr result, straight table expression

Anonymous — Wed, 13 Jul 2016 16:18:29 GMT

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}?

Re: Show aggr result, straight table expression

sunny_talwar — Wed, 13 Jul 2016 16:23:01 GMT

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?

Re: Show aggr result, straight table expression

Anonymous — Wed, 13 Jul 2016 16:42:47 GMT

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

Re: Show aggr result, straight table expression

sunny_talwar — Wed, 13 Jul 2016 16:46:31 GMT

How about now

Re: Show aggr result, straight table expression

Anonymous — Wed, 13 Jul 2016 17:03:31 GMT

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?

Re: Show aggr result, straight table expression

sunny_talwar — Wed, 13 Jul 2016 17:17:24 GMT

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