Skip to main content
Announcements
Meet the 2024 Qlik Luminaries and Partner Ambassadors! READ ON!
cancel
Showing results for 
Search instead for 
Did you mean: 
luca_bellotti
Contributor III
Contributor III

From days to months

Hi everyone,

i have the following table SALES loaded in the script:

NAMEDATESALES
BLUE01/01/20181000
BLUE10/01/20181000
BLUE20/01/20181000
BLUE30/01/20181000
BLUE01/02/20181000
BLUE10/02/20181000
BLUE20/02/20181000

I need a new table SALES_MONTHS like this

NAMEDATESALES
BLUE01/20184000
BLUE02/20183000

Anyone has any suggestion on how to get this done?

Thanks

Luca

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

4 Replies
petter
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

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
Contributor III
Contributor III
Author

Hi Petter,

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

Thanks again.

YoussefBelloum
Champion
Champion

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 ;