Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Include null values in set analysis not working

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:

IF

(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()

IF

(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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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])

View solution in original post

6 Replies
Anonymous
Not applicable
Author

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

sunny_talwar

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]))

Not applicable
Author

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

Anonymous
Not applicable
Author

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])

Not applicable
Author

Does that have to be in load script?

sunny_talwar

The creation of Flag? Yes