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

[#1]

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.

Any advice would be awesome

Thanks!

[CarlosAMonroy]

Hi Danielle,

You can try the following:

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

Hope that helps,

Carlos M

[#3]

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)

[dwforest]

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.

[agigliotti]

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

[CarlosAMonroy]

So then try:

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

Carlos M