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.

[khanashique]

Year and Month is null means...

When the filter is not applied then the first expression of Year(Today()) and Month(today()) should show the result in the table for current Month.

And when the filter is applied for year and month then the second expression should show the result of the selected year and months data.

[khanashique]

i have used date as dimension, Year and Months are filter...

[sunny_talwar]

So which part of the expression is not working? With selection or without selection?

[khanashique]

without selection is not working

[sunny_talwar]

So something wrong with this?

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

Does it work when you use it stand alone?

[khanashique]

Yes it works....

[sunny_talwar]

Lol, I am not sure I understand the issue if both expression work... and your if condition work... I am not sure what happens when they get together :). A sample might help me see what you are having problem with. Else, I am not sure how I can help....

[khanashique]

See attached....

[sunny_talwar]

Here is what I did

Script

Table:
LOAD Date(Date) as Date,
     Year(Date) as Year,
     Month(Date) as Month,
     Line, 
     Planned, 
     Actual, 
     [Plan VS Actual]
FROM
[..\..\Downloads\Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

I used this expression based on the new field name

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

I had to use or because you only had 1 year and the condition was failing. But if your condition is "AND" and you have multiple years... then the above should work the same with "AND" instead of "OR"

When I have not selected anything

When a year and Month is selected

What is the issue here?

P.S. I have tested it out in QlikView... but should work the same way in Qlik Sense

[khanashique]

hi sunny,

it looks good with you, but when i apply the same expression in qliksense. it does not return the below value without selection of year and month, but the same figure gets after selection of year and month