How to Group previous values

[vikasshana]

Hi,

I've the below data.

Load * Inline [

Mon-Year,Date,Sales

July-2023,07/18/2023,10

Aug-2023,08/13/2023,20

Sep-2023,09/07/2023,30

Oct-2023,10/18/2023,40

Nov-2023,11/21/2023,50

Dec-2023,12/26/2023,60

Jan-2024,01/17/2024,70

];

Looking for a script or set analysis expression if we select Date '10/18/2023' it has to group it's previous records and show sales in one line and rest after records has to remain same.

Below is the expected output.

Mon-Year	Date	Sales
July-2023	07/18/2023	60
Oct-2023	10/18/2023	40
Nov-2023	11/21/2023	50
Dec-2023	12/26/2023	60
Jan-2024	01/17/2024	70

Here July, Aug & Sep grouped into one row because I selected 10/18/2023. If I select '11/21/2023' it has to group it's previous records and show sales in one line and rest after records has to remain same.

Below is the expected output.

Mon-Year	Date	Sales
July-2023	07/18/2023	100
Nov-2023	11/21/2023	50
Dec-2023	12/26/2023	60
Jan-2024	01/17/2024	70

Here July, Aug, Sep & Oct grouped into one row because I selected 11/21/2023.

Regards,

V

[JandreKillianRIC]

Hi @vikasshana 

This is my result 

Step 1: Create a variable

vSelectedDate with the value of =Date

Step 2: Create a Table using Mon-Year and Date as Dimensions. 

Step 3: Use the following expression as Grouped Sales

{<Date = >}
Sum(Aggr(IF(Date <= '$(vSelectedDate)', 
	Sum(Total {<Date = {"<=$(vSelectedDate)"}>} Sales), 
    Sum( {<Date = {">$(vSelectedDate)"}>} Sales)), Date))

 Step 4: Add-Ons > Untick Include Zero Values 

Let me know if this sorts out your problem. 

Regards Jandre

[vikasshana]

Thank you so much for the expression, but below is the output which I'm expecting if I select 10/18/2023

Mon-Year	Date	Sales
July-2023	07/18/2023	60
Oct-2023	10/18/2023	40
Nov-2023	11/21/2023	50
Dec-2023	12/26/2023	60
Jan-2024	01/17/2024	70

Currently getting this.

Mon-Year	Date	Sales
Oct-2023	10/18/2023	100
Nov-2023	11/21/2023	50
Dec-2023	12/26/2023	60
Jan-2024	01/17/2024	70