Table (Straight or pivot) how to calculate a measure without some dimensions on the line

StephaneP — Fri, 26 Feb 2021 15:11:00 GMT

Hello,

They ask my a table (with drill/down) with these Dimensions :

- Promotion type
- Brand
- Article
The measures :

- Sales Week 1
- Sales Week 2
- Sales Week 3
- Sales Week 4
- Average Sales per week of the last 12 months
- Evolution between the Sales and the Average Sales (x4)
If my product A is on promotion this month, i must calculated the average sales for this product on last 12 months either in promotion or not.

In my data model a sale is tagged with the promotion type if the date of the sale is between the promotion period. So if i select a promotion type, all the sales with this promotion type are selected. All the sales don't have a promotion type !

My formula is :

sum( {1 <[Date]={"<$(=MonthStart(today())) >=$(=MonthStart(AddYears(today(),-1))) "},[Promotion.Type]= >}[Sales.Qty]) / 52

In the set analysis [Promotion.Type]= is to take all values for promotion type but i must ignore this dimension in my calcul because i want to have the sales that not have this tag too.

Qlik add all cnk that was in promotion last year in my table. The sales Calculated is only the sales on promotion for the products.

Ex :

- Product A is on promotion this month (250 sales this week) but never been in promotion last year. The sales for product A last year was 5200. In my table i have Avg/week : 0 This week : 250 Iwould like to have Avg/week : 100 This week : 250
- Product B is not on promotion this month. The Sales last year was 2600 with 520 in promotion.
In my table Avg/week : 10 This week : 0. I don't want to see product B in my table

How to avoid that ? How to keep only the product in promotion this month and calculate the total sales last year for these products only ?

Thanks in advance,
Bet regards,
Stephane

topic Table (Straight or pivot) how to calculate a measure without some dimensions on the line in App Development

Table (Straight or pivot) how to calculate a measure without some dimensions on the line