Pivot Table Values - while there are sales values

[profilejamesbond]

Hi,

I have Current Sales Value and Previous Value in the Pivot table.

The problem is: when there is no current sales value 12.01.2026 then there is no values for the previous day value while there is previous day sales value. The problem is my assumption Qlik has no value for today then it is like - for the current date and it is not showing anymore for the previous value. While, I see there are values for the 11.01.2026.

I enclosed expression with "alt" while it is not working because there is no values:

alt(sum({<previous_flag = {1}>} sales), 0)

How to fix this situation?

Thanks

[chriscammers]

Could you post a screen shot and give us come context about the measures and dimensions for your pivot?

Also, could you provide some context on how your "previous_flag" is calculated it seems like there could be an issue there too.

[Chanty4u]

Try this 

Sum(

  {

    <Date = {"=$(=Date(Max(Date)-1))"}>

  } sales

)

And Dimensions from calendar, not from fact

[profilejamesbond]

Hi,

I have noticed that expressions are fine the problem is missing data.

Here is the data, where Sales is missing for 07.01.2025 in-fact there is no entry so, I see DK only for 01.01.2025 with sales 101 and there is no entry or data for DK 07.01.2025. So, when I created Pivot table with current and previous Sales I don't see it because the current sales is not there and pick(match expression behave like Qlik Sense default to ignore it because the dimension have no data.

Country, Date, Sales

DK, 01.01.2025, 100

DK, 07.01.2025,  

DK, 12.01.2025, 101 

UK, 01.01.2025, 102

UK, 07.01.2025, 103

So, I figure out the problem I just created dummy data with all the dimensions and place 0 in the Sales.
Now, the problem is there is to much data to replace with 0. Is there anyway to add dummy data. For example: I made a loop like

For each value in '07.01.2025'

concatenate (main_table)

Load 'DK' as Country,

'07.01.2025' as Date

0 as Sales

Autogenerate (1);

Next value

The loop is working as expected but there are so many values to replace and it is not possible to figure them one by one and also possible in the future missing values will arrive. Now, I want to do it in an automated way. Question is how to generate and concatenate missing data into the fact table ? so that expressions can work properly without exceptions.

Thanks

[marcus_sommer · Accepted solution]

Quite common is an approach like the following:

t: load Date from Calendar; join(t) load distinct Country from Countries;

concatenate(Facts) load *, Date & '|' & Country as Key, 0 as Sales
resident t where not exists(Key, Date & '|' & Country);

drop tables t;

to populate missing fact-records. Some adjustments might be wanted, like filtering the calendar to working-days or ... but it would cause some gaps in the dates ... 

[profilejamesbond]

Thank you, @marcus_sommer!

Generated missing data similar to this approach.