COUNT, AGGR, IF AND SUM

sonu8072 — Sat, 16 Nov 2024 01:14:25 GMT

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!!!

Re: COUNT, AGGR, IF AND SUM

sagarjagga — Fri, 21 Feb 2020 12:53:32 GMT

Hi Sonu,

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

Re: COUNT, AGGR, IF AND SUM

sunny_talwar — Fri, 21 Feb 2020 13:08:15 GMT

May be try using Sum instead of Count here

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

topic COUNT, AGGR, IF AND SUM in QlikView

COUNT, AGGR, IF AND SUM

Re: COUNT, AGGR, IF AND SUM

Re: COUNT, AGGR, IF AND SUM