topic Re: How to Group previous values in QlikView

How to Group previous values

vikasshana — Fri, 24 Jan 2025 08:56:50 GMT

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,

Re: How to Group previous values

JandreKillianRIC — Tue, 28 Jan 2025 09:02:34 GMT

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

Re: How to Group previous values

vikasshana — Tue, 28 Jan 2025 20:10:57 GMT

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