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?

[avinashelite]

I don't think your logic will work ...try like this

if(MinString(Type)='A',Sum({<Type={A}>}Amount),Sum({<Type={B}>}Amount))

[Anonymous]

Interesting. This actually works on the sample file but if I apply the same logic on my actual file, the IF statement is returning a false hence always pulling the 2nd statement.

[avinashelite]

Ok what are the values you have in the TYPE column ?

[jonathandienst]

Try

=Alt(Sum({<Type={'A'}>} Amount), Sum({<Type={'B'}>}Amount))

[Anonymous]

The main report has more info in the set analysis:

I am working on a Pivot Table (which is very similar to the layout of what I had attached in the original post). The Dimension are Monthly dates. For the Expression, I am trying to calculate this:

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

but if there if an Amount does not exist then start pulling this:

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

Basically I know that for the first few months, there is an Amount for Type=A, but once that is no longer available, I want the subsequent months on the pivot table to pull the Amount for Type=B.

[avinashelite · Accepted solution]

Ok got you ..

If your data set always associated with only one Type that is A or B then simply use

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

this expression should work

else

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 I understand your first point. Reason I had to cut it is because Type=B also has values even when Type=A exists and I don't want to double count them when both exist hence have to pull Type=A primarily then pull Type=B only when Type=A is null.

Sorry for your script, should there be a parenthesis at the end or is it really structured like that? And Type is outside the set analysis?

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

[avinashelite]

type is outside set Analysis ...if the expression is not working share the app please

[sasiparupudi1]

Try

sum({<Type={'A'}>+<Type={'B'}>}Amount)