6 Replies Latest reply: Apr 11, 2017 3:04 PM by Sunny Talwar

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

Thanks

• Re: Include null values in set analysis not working

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

• Re: Include null values in set analysis not working

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

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

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

Does that have to be in load script?

• Re: Include null values in set analysis not working

The creation of Flag? Yes