Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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.

3 Replies
Highlighted
Creator III
Creator III

Hi Richard,

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

BR

M

Highlighted
Not applicable

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

Highlighted
Creator III
Creator III

Hi, i guess i found it....