topic Pivot table - Null values in QlikView

Pivot table - Null values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a pivot table where i'm displaying YTD amounts as the only expression and have multiple dimensions.

I'm having issues with NULL values in 'CURRENT MONTH ACTIVITY PROFILE' column where i'm asked to display the profile of a user.

As you see in the below screenshot, for Aug-18 User1 has 7count whereas User2 has 0count.

Requirement is User1 should have only value 'CASUAL' as his profile based on current month's count where as User2 should have INACTIVE (if null call it INACTIVE). When I replace NULL with INACTIVE User2 is assigned INACTIVE as expected but User1 has 2 profiles CASUAL and INACTIVE.

Can someone please help me to have only ONE profile per user based on current month counts? Thanks!

Re: Pivot table - Null values

vishsaggi — Tue, 28 Aug 2018 02:52:22 GMT

‌mmay be this for your expr or calculated dimension

= If(Sum(aggr(sum(yourmeasurefield), dim1,dim2,dim3,iserid))>0, ‘Casual’, ‘Inactive’)

Re: Pivot table - Null values

PrashantSangle — Tue, 28 Aug 2018 04:37:02 GMT

how you are calculating "Current Month Active Profile"???

Regards,

Re: Pivot table - Null values

Anonymous — Tue, 28 Aug 2018 09:28:01 GMT

Profile is a pre-calculated field in the database which i'm pulling into Qlikview

Current Month Active Profile: If(DATE >= monthstart(max(DATE)) and DATE <= monthend(max(DATE)), PROFILE, 'INACTIVE')