topic Re: IsNull IsNum Len not working to check if data exists in QlikView

IsNull IsNum Len not working to check if data exists

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: IsNull IsNum Len not working to check if data exists

avinashelite — Wed, 11 Apr 2018 08:55:29 GMT

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

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

Re: IsNull IsNum Len not working to check if data exists

Anonymous — Wed, 11 Apr 2018 09:36:36 GMT

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.

Re: IsNull IsNum Len not working to check if data exists

avinashelite — Wed, 11 Apr 2018 09:41:28 GMT

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

Re: IsNull IsNum Len not working to check if data exists

jonathandienst — Wed, 11 Apr 2018 09:49:36 GMT

Try

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

Re: IsNull IsNum Len not working to check if data exists

Anonymous — Wed, 11 Apr 2018 10:17:21 GMT

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.

Re: IsNull IsNum Len not working to check if data exists

avinashelite — Wed, 11 Apr 2018 10:26:34 GMT

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

Re: IsNull IsNum Len not working to check if data exists

Anonymous — Wed, 11 Apr 2018 10:43:34 GMT

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',

Re: IsNull IsNum Len not working to check if data exists

avinashelite — Wed, 11 Apr 2018 10:46:52 GMT

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

Re: IsNull IsNum Len not working to check if data exists

sasiparupudi1 — Wed, 11 Apr 2018 10:55:31 GMT

Try

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

Re: IsNull IsNum Len not working to check if data exists

Anonymous — Wed, 11 Apr 2018 11:11:47 GMT

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.

Re: IsNull IsNum Len not working to check if data exists

sunny_talwar — Wed, 11 Apr 2018 11:28:00 GMT

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

Re: IsNull IsNum Len not working to check if data exists

Anonymous — Wed, 11 Apr 2018 12:50:36 GMT

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.