5 Replies Latest reply: Jun 15, 2017 9:11 AM by Carlos Monroy

# How to count customers with rolling 12 month sales greater than zero?

I am trying to develop a report showing how many active accounts we have. We define an "active account" as one who has had sales greater than zero in the past 12 months.

I created a rolling 12 month sales variable that works:

vRolling12MoSales = SUM({\$<[Fiscal - Period Id]={">=\$(=vCurrentFiscalPeriodId-12)<=\$(=vCurrentFiscalPeriodId-1)"}>}[Amount - Shipped/Fulfilled USD])

But now, i can't figure out how to nest this expression into an expression to count the number of customers whose rolling 12 month sales are greater than zero.

I tried this formula, but it does not work:

Count({\$<\$(vRolling12MoSales) ={">0"}>} distinct [Deliver/ShipTo - Number])

I have a feeling there may be trouble because my Rolling 12 Mo Sales variable has a sum aggregation in it. But being new to Qliksense, Idon't know how to get around that.

Thanks!

• ###### Re: How to count customers with rolling 12 month sales greater than zero?

Hi Danielle,

You can try the following:

=if(\$(vRolling12MoSales) > 0, count(distinct [Deliver/ShipTo - Number])

Hope that helps,

Carlos M

• ###### Re: How to count customers with rolling 12 month sales greater than zero?

Update: I tried the formula below. If included in a straight table with the account number included in the table, it sums up to the correct number of customers. However, if i get rid of the account number and try to put this equation into a KPI, it returns just all customers who have ever ordered. I can't figure out how to aggregate this without having to include the account number in the table.

IF(\$(vRolling12MoSales)>0,count (distinct [Deliver/ShipTo - Number]),0)

• ###### Re: How to count customers with rolling 12 month sales greater than zero?

So then try:

=IF(\$(vRolling12MoSales) > 0, sum(aggr(count (distinct [Deliver/ShipTo - Number]),AccountNumer)),0)

Carlos M

• ###### Re: How to count customers with rolling 12 month sales greater than zero?

Try:

Count({\$<\$(=\$(vRolling12MoSales)) ={">0"}>} distinct [Deliver/ShipTo - Number])

\$() is expansion, not evaluation, when used in certain places in Qlik the evaluation of the expansion is implicit.

• ###### Re: How to count customers with rolling 12 month sales greater than zero?

you can't use variable or expression in left side of set analysis but only field name.