How to freeze sales of a particular month

[vikasshana]

Hi,

I've below sales information for difference dates.

Sales	Date
1000	20-05-2022
2000	21-05-2022
3000	01-06-2022

So at the end of May the sales value will be 3000. I want this value to be freeze for that particular month. It means if we received another sales values as '4000' in month of June with date as 22/05/2022 I don't want value to be changed from 3000 to 7000, it has to remain as 3000. Looking for set analysis expression.

Regards,

Barat Vikas

@PrashantSangle @sunny_talwar @tresesco 

[Marijn]

I think you'll need an extra field that determines in which month the sales belong. Right now, there's no way to separate the values if the Date field states that it's may. So maybe something like:

Sales	Date	Month_added
1000	20-05-2022	May-22
2000	21-05-2022	May-22
3000	01-06-2022	June-22
4000	22-05-2022	June-22

I'm not sure if this is any help for you, but I don't see any way you could use set expression to select the desired values without an extra field. This way you could do a set expression like this:

sum( {$< Month_added={May-22}>} Sales)

[sidhiq91]

@vikasshana do u get any other column like received date or as of date in ur data set ?

[vikasshana]

@sidhiq91 No other dates in my dataset apart from one data column.

[ASEDavidSu]

=if(sum(sales)>=3000,3000,sum(sales))

[vikasshana]

I took 3000 as an example value, we don't know how much the sales going to be.

[ASEDavidSu]

edit script: Date(Date#(date,'DD-MM-YYYY'),'DD-MM-YYYY') as dates

formula: =sum({1<dates={">=$(=Date(Date#('01-05-2022','DD-MM-YYYY'),'DD-MM-YYYY'))<=$(=Date(Date#('31-05-2022','DD-MM-YYYY'),'DD-MM-YYYY'))"}>}SALES)

[PrashantSangle]

Hi Vikas,

Before providing the solution want to know, how you got to know that the new sales added for previous months?

Also, If it don't want to show sales in that month then in which month you want to show that Sales value?

Solution: You need another date column which help to get you know that value added in which month. If that column is not present in your current dataset, then you have change you incremental load logic, you have to add date column which will capture the load date.

Regards,

Prashant Sangle