Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Dinesh2387
Partner - Contributor II
Partner - Contributor II

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
danilodalosse
Partner - Contributor III
Partner - Contributor III

Hi @Dinesh2387 , 

You may try to do like this:

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

bhargav_bhat
Creator II
Creator II

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
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor II
Partner - Contributor II
Author

Thanks for the reply Danilodalosse!

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

 

bhargav_bhat
Creator II
Creator II

Try This

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

Regards,

Bhargav

danilodalosse
Partner - Contributor III
Partner - Contributor III

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
Partner - Contributor II
Partner - Contributor II
Author


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
Partner - Contributor III
Partner - Contributor III

@Dinesh2387 may have one other problem.

Take a look at my qvf file test at attachment.

Dinesh2387
Partner - Contributor II
Partner - Contributor II
Author

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