Line Chart - Show Months without Sales

[#1]

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:

• Trend sales (for all countries)

    - Dimension: MonthName

    - Measure: Sum(Price)

        ==> All months are correctly shown, even the ones without any sale (e.g. February, April ...):

    

• The second chart reflects the trend of the total sales but excluding US country:

    - 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

[swuehl · Accepted solution]

Try

Sum({$<Country -= {'US'}>}Price) +Sum({1} 0)

[Uday_Pasupuleti]

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