Include null values in set analysis not working

[#1]

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

[Anonymous]

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

[#4]

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

[Anonymous · Accepted solution]

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

[#6]

Does that have to be in load script?

[sunny_talwar]

The creation of Flag? Yes