Can QlikView handle with this?????

[#1]

Hi QliKExperts,

I'm trying to calculate the share of market sold over the shops where my own product is also sold.

For that I need to create a set that only takes into account the Shops where the selected product was sold and evaluate the entire market considering only the calculated set of shops.

Somehow it doesn't read well because it seems to ignore the dimension level on the table, in this case [Month/Year] dimension.

I would be very gratefull if you could contribute with your expertise.

Regards

[#2]

Hi,

As my knowledge change the order of fields in pivot table. u can found in attachment.

Regards,

Chandra

[#3]

Hi Chanred2,

Thanks for your reply, but the solutions doesn't seem so simple as that.

Take for instance the situation where you select ProductId = A1

The Product Sales Column shows the correct result, that is the total sales of the shops selling productid A1, but the 2nd column Sales CAT which should compute the total of the market sold over the shops where productid A1 also sell , also includes ShopId 5 which is wrong, because product A1 doesn't sell any Qty there.

On the table the total market sold over this subset of shops should read 87 instead of the 107 that it shows.

See my point now?

Regards

[johnw · Accepted solution]

Set analysis ignores the values of your table dimensions.

I'm unclear what the CORRECT data should look like, though. It's nice to know which numbers are wrong, but it's even nicer to know what they should be instead, and why.

Making a guess, you've selected A3. In MonthID=1, only shop 1 has sold A3, so you want to see 0 for shops 3 and 4? But for shop 1, you want the sales of ALL products, not just of A3, which is why 35 is right: 25 of A3, and 10 of A1?

Maybe this, then?

if(sum(Qty),sum({<ProductID=>} Qty))

And I'm really not sure what you want in the other table. I'd think you were summing the above across shops, but then 60 seems like the right answer for month 2. Did you just mean that months 1 and 3 were wrong in the other table? Did you want 35, 60 and 35?

sum(aggr(if(sum(Qty),sum({<ProductId=>} Qty)),MonthID,ShopId))

[#5]

Thanks John,

You hit it.

That was exactly the solution I was looking for.

Thank you very much

Cheers