set analysis with isnull function

[khanashique]

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.

[sunny_talwar]

Here is the Qlik Sense sample my friend.

If you still can't get this right, I don't think I will be able to help any further without looking at your dashboard (.qvf file).

[khanashique]

Hi sunny,

Please see attached....

[sunny_talwar]

---

@khanashique wrote:

Hi sunny,

Please see attached....

---

Okay so where is the issue? Would you be able to point me to the incorrect numbers and what the correct numbers should be in the attached Planned Report.qvf?

[khanashique]

Then Number 6081031 was correct number was current month, but if you see the table showing data for all the dates not for current month.

[sunny_talwar · Accepted solution]

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

[christian77]

Follow this advise

Null values are not analyzable. What is a null value? Well, I’ll tell you: Absolutely nothing. Have you tried to filter null values in Sense, or View?

Also, to use set analysis, values are needed. There is no way to tell not null inside a set.

Don’t use ifs. That’s bad.

In your script, replace null values for other real, tangible, filterable…

For example: If is null(Field),’NV’,Field)                                as Rename

Be careful with len, trim and other functions. First analyze them in a table in your app.

Now you can:

                Use set analysis

                Filter

That means you can use Qlik.

I wish you very good luck.

[khanashique]

Thanks a lot, finally it worked...

[khanashique]

Noted, once again lots of Thanks...