topic Re: From days to months in QlikView

From days to months

luca_bellotti — Thu, 19 Jul 2018 09:54:36 GMT

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

Re: From days to months

petter — Thu, 19 Jul 2018 10:14:49 GMT

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

Re: From days to months

petter — Thu, 19 Jul 2018 10:17:14 GMT

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)

Re: From days to months

luca_bellotti — Thu, 19 Jul 2018 10:20:15 GMT

Hi Petter,

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

Thanks again.

Re: From days to months

YoussefBelloum — Thu, 19 Jul 2018 10:24:01 GMT

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 ;