Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
I think you would need to populate the missing values here
You meant to say, While click on ClientB you want to show 1 - 31 days??
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.
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.