Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
khanashique
Contributor II

set analysis with isnull function

Hi,

I am struggling  with the expression below, individual expression works fine, but when i join them with isnull function is does not return any result...

if(isnull(Year) and isnull(Month), 
Sum({<Line = {'Printing'},Year = {"$(=Year(today()))"},Date ={"=Month(today())=Month(Date)"}>}Planned_Qty)
,Sum({<Line = {'Printing'},Year = {"$(=getfieldselections(Year))"},Month = {"$(=getfieldselections(Month))"}>}Planned_Qty))

Need your kind expertise...

Thanks.

1 Solution

Accepted Solutions

Re: set analysis with isnull function

Yes that is because your condition is evaluated on row by row basis. For example....

2019-02-26 has a year of 2019.... IsNull(2019)? No, has a month of 02... IsNull(02), No.... so the expression used = Sum({$<Line = {'Printing'}>} Planned_Qty)

I think you need this condition

=If(GetSelectedCount(Year) = 0 or GetSelectedCount(Month) = 0.....)

or this

=If(GetSelectedCount(Year) = 0 and GetSelectedCount(Month) = 0.....)

 

Num(If(GetSelectedCount(Year) = 0 or GetSelectedCount(Month) = 0,
Sum({$<Line = {'Printing'}, Year = {"$(=Year(Today()))"}, Date = {"=Month(Today()) = Month(Date)"}>} Planned_Qty),
Sum({$<Line = {'Printing'}>} Planned_Qty)
), '#,##0')

27 Replies
anushree1
Valued Contributor

Re: set analysis with isnull function

Please check whether the "IF" condition is being satisfied by taking the if condition alone on a Text box , there could be chances of either of the field not being NULL or maybe its not null but space so you could also try using isnull(trim(<<fieldname>>)) in your "IF " condition.

Partner
Partner

Re: set analysis with isnull function

Hello,

Replace IsNull by a Len ( IF( Len(Year)=0 and Len (Month)=0 ....)) and see if it works.

cordially.

Re: set analysis with isnull function

Why don't you try this?

If(IsNull(Year) and IsNull(Month), 
Sum({<Line = {'Printing'}, Year = {"$(=Year(Today()))"}, Date = {"=Month(Today()) = Month(Date)"}>} Planned_Qty),
Sum({<Line = {'Printing'}>} Planned_Qty)
)
khanashique
Contributor II

Re: set analysis with isnull function

Thanks Sunny for your reply,

The expression you gave i have tried earlier, it does not return the result for first part of expression it returns only second part of the expression and i.e. whole data.

 

thanks once again.

 

khanashique
Contributor II

Re: set analysis with isnull function

hi thanks,

i have applied len (if, it returns zero as value...

 

regards.

Re: set analysis with isnull function

I guess I am not sure the context of the use of your expression. From what I understood, you want to show the second expression when Year and Month are not selected. Is this not true?

khanashique
Contributor II

Re: set analysis with isnull function

Hi,

I wanted to show the data for first expression (for current month), when year and month is null.

Thanks...

Re: set analysis with isnull function

and you have used year and month as dimension?

Re: set analysis with isnull function

Also, if year and month are null... then what does this even mean?

um({<Line = {'Printing'}, Year = {"$(=Year(Today()))"}, Date = {"=Month(Today()) = Month(Date)"}>} Planned_Qty)

Year and Month are Null... why do you expect to see Year = Year(Today()) when it is null? I think you would need to provide a sample to show what you have and provide information of what you are hoping to get