Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Share of Total including not shown items

Hello Community,

I have the following issue: I want to calculate the share of Sales of Articles included in a promotion as a % of the total sales of ALL articles.  As an example I have this:

The raw data is like this:

ArticleWeekSalesPromo (y/n)
a

1

10y
b110y
c110n
a220y
b210y
c220n

I am trying to buid a pivot like this:

Only show the elements of Article where Promo = 'y'

WeekArticle in PromoSalesTotal Sales% of Total Sales
1a103033%
b103033%
2a205040%
b105020%

For the calculation of the Total Sales I have been trying the following

1. When I use:

sum( { < Promo = > }  TOTAL Sales)

I get 80 in each row, which is the grand Total of all Articles, but I want it to be by week.


2. When I use:

sum( { < Promo = > }  TOTAL < Week > Sales)

I get 20 for Week 1 (instead of 30) and 30 for Week 2 (instead of 50)

Now I get the sum by week but not for all articles, but only for the ones shown in the table.


Is there a way of getting the total sum for all articles by week, as in the table above?


Would appreciate your ideas very much


Thanks

Alejandro

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Hi,

Have a look at the application attached.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

Thanks Kaushik,

the thing is that it works as long as the article "c" is shown in the rows.  Is there a way to show only a and b and still have the Total Value from a, b and c in the Total Sales column?

Regards

Alejandro

Highlighted
Creator
Creator

Total sales:

sum(aggr(sum({1}<Week>TOTAL Sales),Week,[Article in Promo]))

The Total will change if you make selections so for every selection you want to be reflected in the total you add the FIELD=p(FIELD) as in:

sum(aggr(sum({1}<Week>TOTAL Sales),Week,[Article in Promo])) Hope it helps!