NVL function in Qlik Sense

[Dinesh2387]

Hi All,

I have requirement  to aggregate Fact column based on the below filter conditions in final layer without touching data model (transform layer) in Qlik Sense. I have used set analysis to achieve it but I got stuck up how to handle the 2nd condition  using NVL function in set analysis.

Oracle:

WHERE    

    "A"."LVL_NM" = 'Consolidated' AND

     nvl("A"."CAT_CD",'Null') NOT IN ('CD') AND

    "A"."STO_NUM" IS NULL AND

    NOT ( "A"."DIV_NM" LIKE '%Headquarters' ) AND

   "C"."GP_NM" = 'Before Taxes' 

Qlik Sense:

Sum({$<[Consolidated] ={'Consolidated'}>
*$<if(isnull([CC]),'Null',[CC])-={'CD'}> // Getting error in expression - Error in Set Modifier expression
*$<[Store Num] = {" "}>
*$<[DivName]-={'*Headquarters*'}>
*$<[GL"" Name]={'Before Taxes'}>
}
[Total Amt])

Any suggestions will be much appreciated!

[danilodalosse]

Hi @Dinesh2387 , 

You may try to do like this:

Sum({<[CC]={"*"}-{'CD'}>} [Total Amt])

[bhargav_bhat]

Hi Dinesh,

In Data load editor or script write
if(isnull(CC),'Null',CC) as CC
if(isnull([Store Num]),'Null',[Store Num]) as [Store Num]


and in front end Qliksense expression write
Sum({$<[Consolidated] ={'Consolidated'},CC-={'Null'} ,
,[Store Num] = {'Null'}
,[DivName]-={"*Headquarters*"}
,[GL"" Name]={'Before Taxes'}
>}
[Total Amt])

HTH
Bhargav

[Dinesh2387]

Bhargav,

Thanks for the Reply!

In my case I dont have privilege to access the model/data load editor, I have to achieve it in final app layer.

Thanks,

Dinesh

[Dinesh2387]

Thanks for the reply Danilodalosse!

The condition Sum({<[CC]={"*"}-{'CD'}>} [Total Amt])  doesn't pull the Null values which are not CD.

[bhargav_bhat]

Try This

Sum({<[CC]-={'CD'}> + <[CC]={"-"}> } [Total Amt]) 

Regards,

Bhargav

[danilodalosse]

Ok, so your fields are null and not empty like my test.

Just try this bellow,

=Sum({<[Total Amt]={"=count([CC])=0"}>}[Total Amt])
+
Sum({<[CC]={"*"}-{'CD'}>} [Total Amt])

[Dinesh2387]

Im getting Calculation timed out error after taking few minutes of time upon applying the provided logic

=Sum({<[Total Amt]={"=count([CC])=0"}>}[Total Amt])
+
Sum({<[CC]={"*"}-{'CD'}>} [Total Amt]).

[danilodalosse]

@Dinesh2387 may have one other problem.

Take a look at my qvf file test at attachment.

[Dinesh2387]

Hi All,

I could able to achieve the desired solution for the requirement. Below is the condition I have used.

Thanks all for the responds

Sum({$<[Consolidated] ={'Consolidated'},

[CC])-={'CD'},

[DivName]-={'*Headquarters*'},

[GL"" Name]={'Before Taxes'}

* 1-$<[DC Store Number]={"*"}>

}

[Total Amt])