Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would very much appreciate your help for the following problem.
I have created 2 tables, sale and calendar , linked by the field "Month" as follows:
Sale:
Load * inline
[ID, Month, Country, Price
1, 1, FR, 100
2, 1, US, 200
3, 3, FR, 120
4, 3, FR, 210
5, 5, FR, 300
6, 5, US, 200
];
Calendar:
Load * inline
[Month, MonthName
1, January
2, February
3, March
4, April
5, May
6, June
7, July
8, August
9, September
10, October
11, November
12, December
];
I want to display 2 line charts:
- Dimension: MonthName
- Measure: Sum(Price)
==> All months are correctly shown, even the ones without any sale (e.g. February, April ...):
- Dimension: MonthName
- Measure: Sum({$<Country -= {'US'}>}Price)
==> The months without sales are not shown:
I wonder if there is a way to reflect these months without sales (e.g. February, April etc...) without changing the data model and the load script, ideally just changing the measure above ( i.e. Sum({$<Country -= {'US'}>}Price)) .
Many thanks in advance for your help!
Annick
Try
Sum({$<Country -= {'US'}>}Price) +Sum({1} 0)
Hi Annick,
Try
Sum( {$<Country = {'US'}>}Price ) + 0 * Sum( {1<Country = {'US'}>}Price )
The Set Analysis in the second term will force Qlik Sense to show all values, but since you multiply it by zero, the value will still be the correct one.
Thanks,
Uday