How to load only date where higher the sum of values in each month

[mahnoor1279]

Hey everyone,

I've task to show the highest amount for  day of month where the sum of value in greater  then rest of the days of that specific month.

TEST1:
LOAD
FUND_NO,
AMOUNT
DATE

FROM [lib://DataExtractor (qsense)/TRANSACTION.qvd]
(qvd);

NoConcatenate
TEST2:

LOAD FUND_NO,SUM(AMOUNT)) AS gross_amount

Resident TEST1
GROUP BY FUND_NO,DATE;
DROP TABLE TEST1;

NoConcatenate
TEST3:

LOAD DATE, MAX(gross_amount) AS gross_amount

Resident TEST2
GROUP BY DATE;
DROP TABLE TEST2;

DESIRED RESULT 

201026113

07/05/2023

764246324

SAMPLE DATA:

FUND_NO	DATE	SUM(AMOUNT)
201026113	02/05/2023	2434246
201026113	03/05/2023	1364210
201026113	04/05/2023	353235
201026113	15/05/2023	15642
201026113	06/05/2023	1258624
201026113	07/05/2023	764246324
201026113	11/05/2023	4574243
201026113	09/05/2023	3457430

[joaopaulo_delco]

Hi, @mahnoor1279!

           If you need to do it in a chart, you can use the expression like the screenshot below:

I also add the qvf file attached. 

[mahnoor1279]

But how can we do it on script editor ?

[joaopaulo_delco · Accepted solution]

Hi @mahnoor1279 

Try the code below:

[TEST]:
LOAD
	FUND_NO,
	AMOUNT,
	DATE,
    MonthName(DATE) as MONTH_YEAR
FROM [lib://Temp/Fonte Help Community.xlsx]
(ooxml, embedded labels, table is Planilha1);

NoConcatenate

[AUX_1]:
LOAD
	FUND_NO,
	Sum(AMOUNT) as TOTAL_AMOUNT,
	DATE,
    MONTH_YEAR
Resident TEST 
group by FUND_NO, MONTH_YEAR, DATE;    
Drop Table TEST;

NoConcatenate
[FINAL]:
LOAD FUND_NO,
     FirstSortedValue(DATE, -TOTAL_AMOUNT) as Data_Top,
     Max(TOTAL_AMOUNT) 					   as TOTAL_AMOUNT
Resident AUX_1 
Group By FUND_NO, MONTH_YEAR;
Drop Table AUX_1;

[mahnoor1279]

Thanks alot