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

[cseward1963]

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. 

[sasis551]

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]

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.