Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik Certification Exam Sale - 40% off. Register by June 30, 2020. GET DETAILS
Highlighted
Partner
Partner

NVL function in Qlik Sense

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!
10 Replies
Highlighted
Partner
Partner

Re: NVL function in Qlik Sense

Hi @Dinesh2387 , 

You may try to do like this:

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

Highlighted
Contributor II

Re: NVL function in Qlik Sense

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

Highlighted
Partner
Partner

Re: NVL function in Qlik Sense

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

Highlighted
Partner
Partner

Re: NVL function in Qlik Sense

Thanks for the reply Danilodalosse!

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

 

Highlighted
Contributor II

Re: NVL function in Qlik Sense

Try This

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

Regards,

Bhargav

Highlighted
Partner
Partner

Re: NVL function in Qlik Sense

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

 

Highlighted
Partner
Partner

Re: NVL function in Qlik Sense


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

Highlighted
Partner
Partner

Re: NVL function in Qlik Sense

@Dinesh2387 may have one other problem.

Take a look at my qvf file test at attachment.

Highlighted
Partner
Partner

Re: NVL function in Qlik Sense

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