7 Replies Latest reply on Apr 27, 2018 5:27 AM by Zhouyi Zhang

# Calculation using filters

Hi All,

I was trying to create a view using sample data attached in which the requirement is to display cumulative count of Rec Id based on filter Action Status.

Basically, Action Status is made of 3 values 'Approved', 'Pending' and 'Implemented' out of which 'Approved' and 'Pending' status is equal to 'Open' in Status Id and 'Implemented' is equal to 'Closed' in Status Id. I would like to have the count of (Open - Closed) Rec Id based on filter Action Status.

Looking at the sample data. (When all the values are selected in Action Filter)

1. Total Counts where Action Status is 'Approved' and 'Pending'  = 33
2. Total Counts where Action Status is 'Implemented' = 20
3. 1-2 = 13 which is total count of Open items now as 20 are already closed

1. Now If I select 'Approved' and 'Pending' separately then it is giving me 17 and 16 respectively as it is calculating out of 33 Open counts
2. Is there any way to calculate the counts out of 13 Open items only which will give me Approved = 6 and Pending = 7

Piyush

• ###### 1. Re: Calculation using filters

Hi Piyush,

hope it helps

Thanks,

Bharat

• ###### 2. Re: Calculation using filters

Thanks Bharat for suggestion, but I tried sets as well. Could you please share an example workbook using sample data set?

Piyush

• ###### 3. Re: Calculation using filters

Hi, Piyush

Please find my solution attached as well as below screenshot for reference.

Hope this helps

ZZ

• ###### 4. Re: Calculation using filters

Hi Zhouyi,

Thanks for your reply, this will work but I need all the counts in a stacked bar based on Action Status filter, something like this.

1. Select All in Action Status filter - it should give me Open = 13 and Closed = 20
2. Select Approved and Implemented in filter - it should give me Open = 6 and Closed = 20
3. Select Pending and Implemented in filter - it should give me Open = 7 and Closed = 20
4. Select Approved and Pending in filter - it should give me Open = 13
5. Select Implemented in filter - it should give me Closed = 20

Hope this is more clear.

Regards,

Piyush

• ###### 5. Re: Calculation using filters

Hi, Piyush

Please find my updated solution attached..

ZZ

1 of 1 people found this helpful
• ###### 6. Re: Calculation using filters

Thanks it worked

• ###### 7. Re: Calculation using filters

No worries, I am glad to help