Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Duckabush
Contributor II
Contributor II

Sum by Month, Year and ID

This is my example data

IDDateAmount
12020-05-10$150.00
1.12020-06-11$900.00
1.22020-05-12$200.00
1.32020-07-13$18.00
1.42020-05-14$500.00
1.52020-06-15$1000.00
 
 
The main ID in example is 1, the number after the period is the sub id. I need to group by ID MONTH-YEAR and sum amount for ID of MONTH-YEAR.
 
For example
 
IDMonthYearSum(Amount)
1JUL-2020$918.00
1JUN-2020$1000.00
1MAY-2020$850.00

 

file:
LOAD
    SUBFIELD(ID, '.'1)  as ID,
    date(INVOICEDATE,'YYYY-MM-DD') as Date,
    month(DATE) & '-' & year(DATE) as MonthYear
FROM [lib://file.qvd](qvd);

 

The second table is

file_amount:
LOAD
    subfield(id, '.'1) as ID,
    sum(Amount) as Amount
FROM [lib://file_amount.qvd](qvd);
WHERE Amount <> '0' AND (CODE = '48' OR CODE = '50') Group By ID;
 
I think part of it is the Group By ID, as that is pulling the 1 and 1.1, But not sure about the rest. Any help would be great.
 
Labels (4)
1 Reply
pradosh_thakur
Master II
Master II

I am not sure hat you  meant in the last but if you want table-> table 2 do the following
 
file:
Load ID,MonthYear,Sum(Amount) as Amount
group by ID,MonthYear;
LOAD
    SUBFIELD(ID, '.'1)  as ID,
    date(INVOICEDATE,'YYYY-MM-DD') as Date,
    month(DATE) & '-' & year(DATE) as MonthYear,
 Amount
FROM [lib://file.qvd](qvd);
Learning never stops.