Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

kiran3733
New Contributor

Sum by Month,

New to QV, QV.bmp

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.

1 Solution

Accepted Solutions
awhitfield
Esteemed Contributor

Re: Sum by Month,

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

7 Replies

Re: Sum by Month,

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,

Add to your Script this line

month(Date) as Month,

and then use this new Field as your Dimension

awhitfield
Esteemed Contributor

Re: Sum by Month,

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

Employee
Employee

Re: Sum by Month,

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.

kiran3733
New Contributor

Re: Sum by Month,

Thank you Andrew.. it worked.

kiran3733
New Contributor

Re: Sum by Month,

Thank you Robert

kiran3733
New Contributor

Re: Sum by Month,

Thank you Agis, all the suggestions worked.

Community Browser