Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ;
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.
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.
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.
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.