Show all values in pivot table even when a dimension is selected

[Anonymous]

Hi there,

I want to create a pivot table that is able to show value by daily date. Please see the output below.

Year     Month     Client          1     2     3     4     5     6     7     8     9     10     ...     31 <- Day

2016     Jan        Client A       1     0     0     1     2     0     1     0     2      1

                         Client B       0     0     0     0     2     0     0     0     0      0

            Feb        Client B       0     0     0     1     1     0     1     0     0      1

                         Client C       0     1     0     0     1     0     0     0     3      1

2017     Jan        Client A       0     4     0     1     2     0     1     0     0      0

                         Client B       0     0     0     0     2     0     0     0     0      1

                         Client C       0     1     0     0     1     0     0     0     1      1

When user select a client, table will show all value per day or zero even it is null. For example of Client B, the result will be.

Year     Month     Client          1     2     3     4     5     6     7     8     9     10     ...     31 <- Day

2016     Jan        Client B       0     0     0     0     2     0     0     0     0      0

            Feb        Client B       0     0     0     1     1     0     1     0     0      1

2017     Jan        Client B       0     0     0     0     2     0     0     0     0      1

I've tried to unselect "Suppress Zero-Values", select "Show All Values", not work for me, where the table show all zero in the last row.

If I amend the expression like IF(SUM({All Dimensions}  Value) <> SUM({My Set Analysis} Value), SUM({My Set Analysis} Value), 0)

that focus the null result to zero. The table will show all years in master calendar table.

Any suggestion?

Thanks for your help.

Regards,

Nicole

[Anil_Babu_Samineni]

I am thinking, It will get default. Would you share screen shot or application while selection Client B. Meantime, can you show us screenshot in it

[Anonymous]

Hi Anil,

Thanks for your reply. I've uploaded a sample. Please see the attached screen-shot. When ClientB is selected, the days with NULL value will be disappeared. But I'm not sure how to show the missing value.

[sunny_talwar]

I think you would need to populate the missing values here

Generating Missing Data In QlikView

[Anil_Babu_Samineni]

You meant to say, While click on ClientB you want to show 1 - 31 days??

[Anonymous]

Yes, if there is null value on the day, then just show as zero. For example of 8th - 9th in my screen-shot, there is no record on that days in client table, then just show as zero.

[Anonymous]

Thanks for your suggestion. I'm afraid that I cannot generate the missing data to QVDs in my case, as I'm processing quite large volume of data.