Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
need help with set expression
Dim: Date in MMM-YY format
Measure Sum(salary),Count(ID)
But the trick over here is if date column has same month and year and has same Status than show sum of both date under that Month year.
Date | Salary | ID | Status |
3/1/2020 | 10 | 1 | A |
3/15/2020 | 12 | 1 | A |
4/1/2020 | 23 | 2 | B |
4/12/2020 | 45 | 1 | B |
6/29/2020 | 89 | 1 | A |
7/1/2020 | 65 | 1 | A |
8/1/2020 | 78 | 1 | B |
expected output
Date | Salary | ID | Status |
Mar-20 | 22 | 2 | A |
Apr-20 | 68 | 3 | B |
Jun-20 | 89 | 1 | A |
Jul-20 | 65 | 1 | A |
Aug-20 | 78 | 1 | B |
Please help!!
created new date field
Date(monthstart(date),'MMM-YY')as newdate
so insight, the newdate field references to monthstart from the dedicated date.
Main:
LOAD
"Date",
Text( Date("Date",'MMM-YY')) as DateY,
Salary,
ID,
Status
FROM [lib://TEST/Dates.xlsx]
(ooxml, embedded labels, table is Sheet1);
LOAD
DateY,Status,
sum(Salary) as Salary,
Count(ID) as ID
resident Main group by DateY,Status;
created new date field
Date(monthstart(date),'MMM-YY')as newdate
so insight, the newdate field references to monthstart from the dedicated date.