topic Re: Filtering the sum/count function in SET ANALYSIS Expression in QlikView

Filtering the sum/count function in SET ANALYSIS Expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi - I'm trying to achieve similar to a SQL having function in Qlikview in set analysis and I need help please.

I''m using this below expression in a pivot table along with other expressions.

With below expression, I'm trying to find the count of sales rep who have produced more than 10k for different combination of filters. The below expression seems to be working fine.

=COUNT({<[Agent_Id] = {"=sum({<New_Sales_Flag = {'Y'}, [Year]={'$(vYear)'}, [Date] = {'<=$(vLastBusinessday) '} >} Sales) >=10000"}>} DISTINCT [Agent_Id] )

I'm having issues with the below sum expression where I'm trying to do the above but calculate the total sales amount for all those agents who did sales more than 10k

=sum({<[Agent_Id] = {"=sum({<New_Sales_Flag = {'Y'}, [Year]={'$(vYear)'}, [Date] = {'<=$(vLastBusinessday) '} >} Sales) >=10000"}>} [Sales] )

Its funny I referred to the reply from Miguel Angel from the below post and achieved the count. But Sum is not working as expected. My sum result number doubles or triples and its not accurate. Any help would be much appreciated.

Functional filtering in Set Analysis

Thank you

Re: Filtering the sum/count function in SET ANALYSIS Expression

ogautier62 — Sat, 30 Jun 2018 05:18:23 GMT

Hi,

in your expression above you filter agent with your criteria,

but once agent is filtered, sales are not

I think you ought to add a filter too for sales

regards

Re: Filtering the sum/count function in SET ANALYSIS Expression

Anil_Babu_Samineni — Sat, 30 Jun 2018 05:39:39 GMT

Can you check this first and show the value?

=sum({<New_Sales_Flag = {'Y'}, [Year]={'$(vYear)'}, [Date] = {'<=$(vLastBusinessday) '} >} Sales)

Re: Filtering the sum/count function in SET ANALYSIS Expression

Anonymous — Mon, 02 Jul 2018 23:22:23 GMT

This gives me the total sales for the dimension. But I need the total sales for the agents who have done above 10k.

Re: Filtering the sum/count function in SET ANALYSIS Expression

Anonymous — Tue, 03 Jul 2018 01:43:15 GMT

Thank you for your help. I tried with the below and it works. I had to use the aggr function.

=sum(aggr(if(

sum(aggr(sum( {< New_Sales_Flag= {'Y'}, [Year]={'$(vYear)'}, [Date] = {'<=$(vLastBusinessday) '}>} [Sales] ), [sales_Rep])) >= 10000,

sum( {< New_Sales_Flag = {'Y'}, [Year]={'$(vYear)'}, [Date] = {'<=$(vLastBusinessday) '}>} [Sales] ),

0),

sales_Rep]))

Re: Filtering the sum/count function in SET ANALYSIS Expression

Anil_Babu_Samineni — Tue, 03 Jul 2018 05:00:59 GMT

Perhaps this?

=sum({<New_Sales_Flag = {'Y'}, [Year]={'$(vYear)'}, [Date] = {'<=$(vLastBusinessday) '}, Sales = {"=Sum({<New_Sales_Flag = {'Y'}, [Year]={'$(vYear)'}, [Date] = {'<=$(vLastBusinessday) '}>} Sales)>10000"} >} Sales)