IsNull IsNum Len not working to check if data exists

[Anonymous]

Hi,

I have tried the usual functions (IsNull, IsNum, Len) but unable to correctly set up an Expression in my pivot table whereby if the sum({<Type={'A'}>}Amount) is not existing for that Dimension then use sum({<Type={'B'}>}Amount).

Please see attached. Any thoughts?

[Anonymous]

Yeah, interestingly not working. Will take some time for me to strip down the app. I'll try to provide later. Thanks for the help.

[sunny_talwar]

Does this expression work for you?

Sum({<DateNum = {">=$(VariableDate)"},[Type]={'A'},[Category]={'AAA'}>}Amount)

I have a feeling that this might not be working also because of the Set Analysis on the DateNum, but I might be wrong...

EDIT: Try one of these

if(MinString({<DateNum = {">=$(=VariableDate)"},[Category]={'AAA'}>}Type)='A',

Sum({<DateNum = {">=$(=VariableDate)"},[Type]={'A'},[Category]={'AAA'}>}Amount),

Sum({<DateNum = {">=$(=VariableDate)"},[Type]={'B'},[Category]={'AAA'}>}Amount))

or this

if(MinString({<DateNum = {">=$(=$(VariableDate))"},[Category]={'AAA'}>}Type)='A',

Sum({<DateNum = {">=$(=$(VariableDate))"},[Type]={'A'},[Category]={'AAA'}>}Amount),

Sum({<DateNum = {">=$(=$(VariableDate))"},[Type]={'B'},[Category]={'AAA'}>}Amount))

[Anonymous]

Yes on its own, the expression works with the set analysis. Anyway really strange that the MinString approach doesn't work on the main app, but on a simple file (and I also replicated all the set analysis), the MinString function is working properly as intended. I'll just close this for now as the proposed solution is technically working. May just have something to do with my data.