Use RangeSum and don't count duplicates

[MRitter]

I have a line chart that has the following metric:

Rangesum( Above(Count
(Distinct {<ControlorMail={'C'},TotRetailQuantity = {">=1"},transaction_dt = {">=$(=CampaignStartDate)<=$(=CampaignEndDate)"}>} brand_customer_id)
, 0, RowNo()))

The dimension is the transaction_dt.

Works great except when I have a customer who shops on multiple dates.  I only want to count them one time.  But currently I am counting them each time.  

Not sure what modification I need to the above to only count them a single time?

[sunny_talwar]

Best way to handle this would be to use The As Of Table

[Anil_Babu_Samineni]

Try this way

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

[MRitter]

Not sure how to approach this.  

I want to show every transaction date.  I only want to count the customer one time over the period even if they shopped more than one time.

So I am not seeing how building a table of dates will accomplish this?

Can you possibly post an example?

[sunny_talwar]

Here is an example...

Table:
LOAD * INLINE [
    Date, customer_id
    1/1/2019, 1
    1/2/2019, 1
    1/2/2019, 2
    1/3/2019, 2
    1/3/2019, 3
];

MinDate:
LOAD Min(Date) as MinDate
Resident Table;

LET vMinDate = Peek('MinDate');
TRACE $(vMinDate);

AsOfTable:
LOAD DISTINCT Date as AsOfDate,
	 Date - (IterNo()-1) as Date
Resident Table
While Date - (IterNo()-1) >= $(vMinDate);

DROP Table MinDate;

Now if I use RangeSum(Above()) I get this

But with AsOfDate, I get this

Does it make sense now?

[MRitter]

That does make sense.  Thank you.

Now I just need to figure out how to convert that logic to my situation.

[MRitter]

Another question.  My transaction file will contain millions of rows.  Will creating this table be a performance issue?

[sunny_talwar]

It will have some impact, but since we are looking at distinct date.. we should not have a huge impact. Also, right now I am creating a asofdate based on the fact that cumulation is not a YTD or MTD... but if you only accumulate YTD or MTD, you can improve the code to make sure that you are not creating 01/01/2005 for 05/13/2019 assuming 01/01/2005 is your min date. Things like those will make your data model better. But even without them... it is better to create asoftable compared to doing this directly on the front end of the app using rangesum and above

[MRitter]

Should this table be an island or should it associate back to the transaction table that it was built from?

[MRitter]

This is what I changed my script to and it is not working correctly.

Table:
LOAD
transaction_dt as trans_date,
brand_customer_id as customer_id,
ControlorMail as CorM,
TotRetailQuantity as RetailQty
Resident Transactions;

MinDate:
LOAD Min(trans_date) as MinDate
Resident Table;

LET vMinDate = Peek('MinDate');
TRACE $(vMinDate);

AsOfTable:
LOAD DISTINCT trans_date as AsOfDate,
trans_date - (IterNo()-1) as trans_date
Resident Table
While trans_date - (IterNo()-1) >= $(vMinDate);

DROP Table MinDate;

This is what my chart is looking like.  It is not acting like a running total.