Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
sridhar_sigired
Creator
Creator

Show Null values for Day of Week

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.

5 Replies
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 ;


talk is cheap, supply exceeds demand
sridhar_sigired
Creator
Creator
Author

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
Creator
Creator
Author

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.


talk is cheap, supply exceeds demand
sridhar_sigired
Creator
Creator
Author

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.