topic P Function not aggregating as i'd like it to... in QlikView

P Function not aggregating as i'd like it to...

Mon, 08 Dec 2014 12:12:52 GMT

Hi there,

This query has been irritating me for a couple of days now. Help would be much appreciated.

I am trying to get a market share calculation right. I have simplified my problem and put it into a QVW demo file.

In this file you will see that there are 2 stores with 2 separate promotions for a supplier. All i would like to do (as seen in the top straight table) is calculate the Promotional Product vs Total Market.

NOTE: the total market is the total category i.e. a=forks, b=knives.........

In the 'Marketshare Analysis' chart the [Copper Focus Brand Sales] is correct .

The 'Complete Basket Sales' is wrong. Which makes the rest of the chart incorrect.

The way in which i get a correct Complete Basket is by making the following selection within the model (from cleared state):

1. select a store,

2. select promotionalflag = yes,

3. select supplier = coppercorp,

4. select possible Categories,

5. deselect PromotionalFlag and Supplier.

This will give you the directly competing market for the promotional items.

I hope this is clear to you, it is quite difficult to explain but in my chart - Complete Basket Sales should balance to the above selection criteria but i think the P function is selecting a wider range.

What i need to do is somehow aggregate my P function by store...

Any Suggestions?

Thanks in advance.

Re: P Function not aggregating as i'd like it to...

mato32188 — Mon, 08 Dec 2014 13:54:39 GMT

Hi Richard,

try sum(aggr(sum(Sales), Store, Category, PromotionFlag, Supplier))

Re: P Function not aggregating as i'd like it to...

Mon, 08 Dec 2014 14:05:37 GMT

Hi Martin,

Thanks for your reply. Unfortunately this is not correct. My current query is as follows:

sum({$<Category = P({1<Supplier = {'coppercorp'},PromotionFlag = {'Yes'}>})>}Sales)

I am trying to get all the Categories where there is a Promotional Product. e.g. in Store 1 Category C is not meant to show up because coppercorp did not compete in that Category.

The above query seems to be returning all Competing Categories across all Stores ignoring the fact that e.g. Store 1 did not compete in Category C.

I tried this:

sum(aggr(sum({$<Category = P({1<Supplier = {'coppercorp'},PromotionFlag = {'Yes'}>})>}Sales),Store))

but i think aggr and the P function clash in this instance.

Thanks

Re: Re: P Function not aggregating as i'd like it to...

arthur_dom — Mon, 08 Dec 2014 19:54:16 GMT

Hi, i guess i found it....