topic Re: Include null values in set analysis not working in QlikView

Include null values in set analysis not working

Tue, 11 Apr 2017 18:43:20 GMT

Hi All,

I am using the following code. I realized some values do not have a year associated and I want to show these. My initial code was this:

(ISNULL(GETFIELDSELECTIONS([Year])),

Sum({<[Year]={'($(vCurrentYear))', '($(vPrevYear))','($(v2PrevYear))'}, [Ledger Code]={'1','23','63'}>} [PO Line Amount Billed Functional]),

Sum({<[Year]={'=GETFIELDSELECTIONS([Year])'}, [Ledger Code]={'1','23','63'}>} [PO Line Amount Billed Functional]))

I added the following in red()

(ISNULL(GETFIELDSELECTIONS([Year])),

Sum({<[Year]={'($(vCurrentYear))', '($(vPrevYear))','($(v2PrevYear))', "*"}, [Ledger Code]={'1','23','63'}>} [PO Line Amount Billed Functional]),

Sum({<[Year]={'=GETFIELDSELECTIONS([Year])'}, [Ledger Code]={'1','23','63'}>} [PO Line Amount Billed Functional]))

I'm not sure how else to go about this. Suggestions?

Thanks

Re: Include null values in set analysis not working

Anonymous — Tue, 11 Apr 2017 18:53:38 GMT

do you want to show only the fields, where year is null()?

Re: Include null values in set analysis not working

sunny_talwar — Tue, 11 Apr 2017 18:54:40 GMT

May be create a flag in the script for when Year is not available

If(Len(Trim(Year)) = 0, 1, 0) as Flag...

and then may be this

IF(ISNULL(GETFIELDSELECTIONS([Year])),

Sum({<[Year]={'($(vCurrentYear))', '($(vPrevYear))','($(v2PrevYear))'}, [Ledger Code]={'1','23','63'}>+<Flag = {1}, [Ledger Code]={'1','23','63'}>} [PO Line Amount Billed Functional]),

Sum({<[Year]={'=GETFIELDSELECTIONS([Year])'}, [Ledger Code]={'1','23','63'}>} [PO Line Amount Billed Functional]))

Re: Include null values in set analysis not working

Tue, 11 Apr 2017 18:55:06 GMT

those and the three year variables above. So null + the current year + Year before + 2 years before.

Re: Include null values in set analysis not working

Anonymous — Tue, 11 Apr 2017 18:58:29 GMT

Sum({

<[Year]={'($(vCurrentYear))', '($(vPrevYear))','($(v2PrevYear))'}, [Ledger Code]={'1','23','63'}>

+ (<[Ledger Code]={'1','23','63'}>-<[Ledger Code]={'1','23','63'},Year={"*"}>)

} [PO Line Amount Billed Functional])

Re: Include null values in set analysis not working

Tue, 11 Apr 2017 18:59:37 GMT

Does that have to be in load script?

Re: Include null values in set analysis not working

sunny_talwar — Tue, 11 Apr 2017 19:04:53 GMT

The creation of Flag? Yes