COUNT, AGGR, IF AND SUM

[sonu8072]

Hi Friends!

I have a data set which has Customer wise Sales with Zone, Region and Product [in multiple rows as Invoiced]. I am trying to have an expression that gives me Count of Customers which are above 500 Sales Month till Date in the below table -

Dimensions - Zone | Region | Product |

Expressions - Total Customers Sold [#] | >500 Customers Sold [#]

I have tried [unsuccessfully] to achieve the same with the following expression -

COUNT(AGGR(IF(SUM({<MonthYear={'Feb-2020'}>}Sales)>=500,1,0),Zone,Region,Product,Customer))

Need to figure out where to put the IF statement correctly or any other alternative options to execute the same. Appreciate the help in advance!!!

[sagarjagga]

Hi Sonu,

Could you please share expected result as well in the data excel.

[sunny_talwar · Accepted solution]

May be try using Sum instead of Count here

Sum(Aggr(
    If(Sum({<MonthYear = {'Feb-2020'}>} Sales) >= 500, 1, 0)
, Zone, Region, Product, Customer))