Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
i have the following table SALES loaded in the script:
NAME | DATE | SALES |
---|---|---|
BLUE | 01/01/2018 | 1000 |
BLUE | 10/01/2018 | 1000 |
BLUE | 20/01/2018 | 1000 |
BLUE | 30/01/2018 | 1000 |
BLUE | 01/02/2018 | 1000 |
BLUE | 10/02/2018 | 1000 |
BLUE | 20/02/2018 | 1000 |
I need a new table SALES_MONTHS like this
NAME | DATE | SALES |
---|---|---|
BLUE | 01/2018 | 4000 |
BLUE | 02/2018 | 3000 |
Anyone has any suggestion on how to get this done?
Thanks
Luca
In the load script you can do this if you already have the table loaded as TABLE1:
TABLE2:
LOAD
NAME,
Date(MonthStart(DATE),'MM/YYYY') AS DATE,
Sum(SALES)
RESIDENT
TABLE1
GROUP BY
NAME, MonthStart(DATE);
DROP TABLE1;
or directly from the source:
LOAD
NAME,
MonthStart(DATE) AS DATE,
Sum(SALES)
FROM
[........] (......)
GROUP BY
NAME, MonthStart(DATE);
In the load script you can do this if you already have the table loaded as TABLE1:
TABLE2:
LOAD
NAME,
Date(MonthStart(DATE),'MM/YYYY') AS DATE,
Sum(SALES)
RESIDENT
TABLE1
GROUP BY
NAME, MonthStart(DATE);
DROP TABLE1;
or directly from the source:
LOAD
NAME,
MonthStart(DATE) AS DATE,
Sum(SALES)
FROM
[........] (......)
GROUP BY
NAME, MonthStart(DATE);
If you prefer you can rather do it in the charts directly in your app without creating an additional table in the load script:
Create two dimensions:
NAME and =Date(MonthStart(DATE),'MM/YYYY')
create a measure:
Sum(SALES)
Hi Petter,
thank you very much, for your help. I prefer to create an additional table for other purposes.
Thanks again.
Or this:
LOAD NAME,
Date#(Date(DATE,'MM/YYYY'),'MM/YYYY') as DATE,
MonthStart(DATE) AS DATE,
sum(SALES) as SALES
FROM...
Group by Date#(Date(DATE,'MM/YYYY'),'MM/YYYY'),
NAME ;