Filtering the sum/count function in SET ANALYSIS Expression

[Anonymous]

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

[ogautier62]

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

[Anil_Babu_Samineni]

Can you check this first and show the value?

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anil_Babu_Samineni]

Perhaps this?

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