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

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

    Danielle Sheffer

      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