Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On-Demand Webinar: Learn proven best practices for a smooth transition to Qlik Sense® SaaS. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
sam1508
Contributor
Contributor

Set expression with If Statement in a expression

Hello all,

I would like to exclude Region filter from my expression but the below listed expression is not working. I have If condition for an aggregated dimension and trying to exclude Region filter. Please suggest.

= Count({<Region= >} Aggr (If ( Status = 'InProcess', Status <> 'Complete'), Commodities, Commodities))

 

Thanks.

Labels (1)
6 Replies
chrismarlow
Specialist II
Specialist II

Hi,

I am not sure the IF formula here is doing what you think it is doing, for If statement would expect to see 3 parts (maybe 2 if value for false is null) and only 1st part would have a comparison ... if Status=InProcess then automatically Status<>Complete ...

Cheers,

Chris.

sam1508
Contributor
Contributor
Author

Hello,

Yes, you are correct. It is not doing the desired behavior. I would like to write an expression in which I want to count 'InProcess' status for a Commodity if the status is not Completed. There may be additional records for the  3rd Status (Void) for that Commodity but I don't have to account for that . Can you please suggest the right expression. I wrote the below expression, it is not giving me the desired count and Region filter is also working which I don't want. 

= Count({<Region= >} Aggr (If ( BCet_Status = 'InProcess' and BCet_Status <>'Complete' or BCet_Status = 'Void' , BCet_Status ), B_Commodities, B_Commodities))

Thanks,

Sam

anat
Specialist II
Specialist II

 

aggr(count({<Region=,Status={'InProgress'}>}Commodities),Commodities)

 

sam1508
Contributor
Contributor
Author

This is not working as expected. It won't check for other records for the same commodity with different Status. I would like to write an expression in which I want to count 'InProgress' status for a Commodity if the status is not Completed. There may be additional records for the  another Status (Void) for that Commodity but I don't have to account for that .

Also, it should count all the distinct InProgress commodities once if the condition satisfies. For below table, 

Comm1 doesn't have Complete Status so it should get counted as 1 for InProgress

Comm2 doesn't have InProgress status so it won't get counted.

Comm3 has Complete and InProgress both so we don't need to count as it is already Completed

Comm4 has InProgress so we need to count it as 1

So, total InProgress Status count for the below table is 2. Hope this example helps 

 

Commodities Sub Commodity Status
Comm1 SC1 Void
Comm1 SC2 InProgress
Comm1 SC3 InProgress
Comm2 SC1 Void
Comm2 SC2 Complete
Comm3 SC1 Complete
Comm3 SC2 InProgress
Comm4 SC1 InProgress
Shepherd69
Contributor III
Contributor III


@sam1508 wrote: MCDVOICE

Hello all,

I would like to exclude Region filter from my expression but the below listed expression is not working. I have If condition for an aggregated dimension and trying to exclude Region filter. Please suggest.

= Count({<Region= >} Aggr (If ( Status = 'InProcess', Status <> 'Complete'), Commodities, Commodities))

 

Thanks.


Modifiers are used to make additions or changes to a selection. Such modifications can be written in the set expression. A modifier consists of one or several field names, each followed by one or several selections that can be made in the field. Modifiers begin and end with angle brackets, <>.

chrismarlow
Specialist II
Specialist II

Hi,

Missed this, maybe;

20220215_1.png

Then just cancel your region selection as I think you got.

Cheers,

Chris.