Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Chris_T-M
New Contributor

Include Dimensions with NULL data in Pivot-Table (e.g. always show 12 months in columns)

Hi community,

I'd like a Pivot-Table to _always_ show 12 months in columns and 3 years in rows.

However, if I filter e.g. on a customer who doens't purchase in every period, the pivot excludes any month/year the customer did not purchase anything.

Any idea how to force Qlik to always display 12+3?

N.B.: "inlude NULL values" is activated for both dimensions and the entire app.
N.B.2: Generating "Dummy" records with zero values for every combination is no option due amount of data

Rgds,

Chris

Tags (2)
Labels (3)
1 Reply
Highlighted
Partner
Partner

Re: Include Dimensions with NULL data in Pivot-Table (e.g. always show 12 months in columns)

hi

one work around you could try is something like this 

sum({<Customer=>}aggr({<Customer=>}count({<Customer=>}distinct if(index(vCustoemrs,Customer)>0,[Line Desc 1])),Customer,Year,Month))

which igonres the selections but them find only the customers who in the selection 

the vCustomer variable is populated with this expression

=chr(39) & GetFieldSelections(Customer,chr(39)& ',' & chr(39)) & chr(39)