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

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

Anonymous — Sat, 16 Nov 2024 07:06:57 GMT

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

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

lironbaram — Thu, 06 Dec 2018 09:35:53 GMT

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)