Assign Number for every ID for every month

[qlikwiz123]

I have below columns in my table.

Purchaser ID

Purchaser Name

Sale ID

Sale Date

Sale Amount

I need to create a new column where if the Purchaser has made a sale on any Sale Date, the column shows value 1 and so on.

Example, if Purchaser ID 25 has different Sale IDs in 1/20/2021, 2/5/2021, 3/14/2021, the new column must have 1,1,1 for each Sale ID-Sale Date-Purchaser ID combination

[edwin]

you can get the distinct of the ID and DATE, then add a rowno() this will assign a unique number per ID/Date combination (that is if you dont care that for one ID, it starts at 1 and another ID its starts at 1000).  then inner join to your data.

[qlikwiz123]

@edwin  Thanks. But I need them to have some value instead of 1 and 1000 so that I can count how many Distinct ID/Date/SaleID combinations are there, to show the frequent Purchaser IDs who make frequent sales.

If Purchaser ID -25 makes 3 sales across 3 different dates, and each row has value 1 assigned, then i can add them up in the end (1+1+1) and say that Purchaser ID 25 frequency is 3.

[edwin]

if what you really wanted was count the number of sales, use count(Sale Amount)  maybe it is best to start with business requirements and not with what you think the solution is

[deshikas]

Count(Distinct SaleDate)

[MayilVahanan]

Hi @qlikwiz123 

You can create a new field

Purchaser ID

Purchaser Name

Sale ID

Sale Date

Sale Amount

1 as NoOfSales

In front end, you can use

sum(NoOfSales)