Show Null values for Day of Week

[sridhar_sigired]

Hi,

I have a pivot table and it is showing day wise total sales in each week. However it is showing only when data is available.

I need to show all days in a week (1-7days) with NULL or Zero values.

Attachments may useful to refer my issue, can anyone help me here. Weekday field should show all days though data is not available.

[Gysbert_Wassenaar]

Disable the Suppress Zero-values option on the Presentation tab. Then use this load script and reload your document:

Data:

LOAD

     Company,

     WeekDay,

     Amount,

     WeekEnd

FROM

     data.xls  (biff, embedded labels, table is Sheet1$)

WHERE

     WeekEnd>= '08/12/2016';

Temp:

LOAD FieldValue('WeekDay', RecNo()) as WeekDay AutoGenerate FieldValueCount('WeekDay');

OUTER JOIN (Temp)

LOAD FieldValue('WeekEnd', RecNo()) as WeekEnd AutoGenerate FieldValueCount('WeekEnd');

OUTER JOIN (Temp)

LOAD FieldValue('Company', RecNo()) as Company AutoGenerate FieldValueCount('Company');

CONCATENATE (Data)

LOAD *, 0 As Amount RESIDENT Temp;

DROP TABLE Temp ;

[sridhar_sigired]

Thanks for your respose.

Actually i have many fields in my table, here i have given example.

Do i need to do same for all fields like above? I have tried but not working.

[sridhar_sigired]

I have tried to add this to my expression

+Avg({<DAY_OF_WEEK=p(DAY_OF_WEEK)>}0)

but i can able to get only 2-6 days.

[Gysbert_Wassenaar]

Yes, if you really want the result you say you want. Every combination of all possible dimension values has to exist in the table in the data model.

[sridhar_sigired]

Thank you, i will retry once again.

I have tried to add this to my expression

+Avg({<DAY_OF_WEEK=p(DAY_OF_WEEK)>}0)

but i can able to get only 2-6 days.