topic Re: Sum by Month, in QlikView

Sum by Month,

Anonymous — Tue, 16 Jun 2015 12:04:45 GMT

New to QV,

My bar chart shows sum of sheets per day, How do i change the chart to show the sum of sheets per month?

Excel sheet with the sample data is attached. Any help would be nice.

Re: Sum by Month,

alexandros17 — Tue, 16 Jun 2015 12:31:00 GMT

When you load data.xlsx add a new field,

Load

...

Year(Data)*100+Month(data) as YM

Resident

...

YM represent the union of year and month eg. 201504, use that field as dimension ...

let me know

Re: Sum by Month,

robert_mika — Tue, 16 Jun 2015 12:40:07 GMT

Add to your Script this line

month(Date) as Month,

and then use this new Field as your Dimension

Re: Sum by Month,

awhitfield — Tue, 16 Jun 2015 12:47:30 GMT

Hi Kiran,

change script to

Data:
LOAD Date(Date) as Date,
Month(Date) as Month,
Sheets,
COMPANY
FROM

(ooxml, embedded labels, table is Sheet1);

Adding the Month Section, then you get:

With Month as the dimension and Sum(Sheets) as the expression

HTH Andy

Re: Sum by Month,

Agis-Kalogiannis — Tue, 16 Jun 2015 13:00:40 GMT

Or if you can't change the script you can create a calculated dimension in your chart, using the expression =month(Date), with label Month and use the same expression.

This will aggregate the sheets by Month

Less efficient that changing the script though, so if you have the option, go for the script change.

Re: Sum by Month,

Anonymous — Wed, 17 Jun 2015 04:54:16 GMT

Thank you Andrew.. it worked.

Re: Sum by Month,

Anonymous — Wed, 17 Jun 2015 04:55:06 GMT

Thank you Robert

Re: Sum by Month,

Anonymous — Wed, 17 Jun 2015 04:55:47 GMT

Thank you Agis, all the suggestions worked.