Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
cseward1963
Contributor

Qlik Sense Suppressing a column with null or n/a values

I'm displaying year to year total client billing in a pivot table.  I show 3 columns for each school year.

1. Sum(Billling)

2. difference between current year and prior year

3. difference % between current year and prior year

to get the difference, I use the "Before" function like this:

sum(Billed) - before(sum(Billed)

the oldest school year has no "before" data so the difference and difference% columns always show "n/a" for the oldest school year.   I would like to suppress the column if there is no "before" to compare with.

I'm thinking that I can use the "Show Column If" option for the difference and difference% columns, but I'm not clear on what kind of expression to enter.  OR, maybe there is another way accomplish the column suppression??

I hope this all makes sense and I hope someone can help me.  Smiley Happy

2 Replies
sasis551
New Contributor III

Re: Qlik Sense Suppressing a column with null or n/a values

Hi,

1)using set analysis you can go like this

a)create  variables  =>crtl + alt +v =>vcuryear=year(today())

                                                                    =>vprevyear=year(today())-1

b)if year dimension is there can directly use it or else create year(date) as year.

c)expression=>sum({<year={$(vcuryear)}>}Billing)-sum({<year={$(vpreyear)}>}Billing)

timpoismans
Valued Contributor

Re: Qlik Sense Suppressing a column with null or n/a values

You can try to use the following expression:

If(IsNull(sum(Billed) - before(sum(Billed)),0,1)

 

I haven't been able to test the column hiding out myself, but the above expression should result in a 0 if the measure returns Null(), or a 1 if it returns a meaningful value. Based on 0 or 1, the column should be hidden or not.