Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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 ;