How to aggregate project sale from sale_id to distinct customers?

[#1]

I have some difficulties when I try to use my Qlik expression to count distinct customers instead of sale_ids.

The data model is not complicated. My fact table consists of sales data, where each sale_id have two records, one from when the sale became active and one from when the sale was finished (a sale in this case resembles a project). Thus, when a new sale is created, I will record it in my model as an active sale and I keep track of the date from when it became active. When the sale closes, I generate a new record in my model and keep track of the closing date.

In order to find active sales at a specified date, I count all sale_ids that are tagged as active (firstStatus=1) and have an active date (Dato) less than max(Dato). Next I subtract all the sale_ids that have been flagged as active but have been finished (closed, i.e. Status=2) before max(Dato).

count({$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>} DISTINCT Sale_id)-
count({$<Dato={"<=$(=Max(Dato))"},Status={2},Sale_id=p({<Dato={"<=$(=Max(Dato))"},firstStatus={1}>}Sale_id)>} DISTINCT Sale_id)

The above expression works in my model. However, I struggle with the above expressions when counting customers, as follows

count({$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>} DISTINCT Customer)-
count({$<Dato={"<=$(=Max(Dato))"},Status={2},Sale_id=p({<Dato={"<=$(=Max(Dato))"},firstStatus={1}>}Sale_id)>} DISTINCT Customer)

When I count distinct customer, the above formula gives me zero active customer for every customer that have e.g. two open sales and where only one of them are closed. However, in this scenario I would have liked the expression to show one active customer.  

I have tried using Set operators instead of two counts, but that does not give me the desired result.

count({$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>-
<Dato={"<=$(=Max(Dato))"},Status={2},Sale_id=p({<Dato={"<=$(=Max(Dato))"},firstStatus={1}>}Sale_id)>} DISTINCT Customer)

I have attached a simplified version of my model. In this model when choosing Date=04.01.2017, customer A should be counted as one active customer. 

Any help would be much appreciated.

Regards.

[sunny_talwar]

May be this?

Sum({$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>} Aggr(RangeSum(count({$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>} DISTINCT Sale_id),

-count({$<Dato={"<=$(=Max(Dato))"},Status={2},Sale_id=p({<Dato={"<=$(=Max(Dato))"},firstStatus={1}>}Sale_id)>} DISTINCT Sale_id)), Sale_id, Customer))

[#3]

Thanks for the reply! It seems reasonable that we need to use an AGGR function in this scenario. However, now customer E is counted twice, since the cusomter has two active sales. But I would like to only count customer E once as well.

I have tried adding DISTINCT to the expression as follows, but that does not do the trick, it seems.

Sum(DISTINCT{$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>} Aggr(RangeSum(count({$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>} DISTINCT Sale_id),

-count({$<Dato={"<=$(=Max(Dato))"},Status={2},Sale_id=p({<Dato={"<=$(=Max(Dato))"},firstStatus={1}>}Sale_id)>} DISTINCT Sale_id)), Customer,Sale_id))

Now it looks like customer E is only counted once, but total customers adds up to only 1, not 6.

[sunny_talwar · Accepted solution]

This expression might be simplified, but this seems to work

Sum({$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>}Aggr(Sum(DISTINCT {$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>} Aggr(RangeSum(count({$<Dato={"<=$(=Max(Dato))"},firstStatus={1}>} DISTINCT Sale_id),

-count({$<Dato={"<=$(=Max(Dato))"},Status={2},Sale_id=p({<Dato={"<=$(=Max(Dato))"},firstStatus={1}>}Sale_id)>} DISTINCT Sale_id)), Sale_id, Customer)), Customer))

[#5]

Thanks! The expression works in my original app as well.