From days to months

[luca_bellotti]

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

[petter · Accepted solution]

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);

[petter]

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)

[luca_bellotti]

Hi Petter,

thank you very much, for your help. I prefer to create an additional table for other purposes.

Thanks again.

[YoussefBelloum]

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 ;