Calculated Dimension?

[peterderrington]

I'm trying to create a line graph in QlikView to compare data across two years.

I have a dimension (DOA_Month) and an expression (AVG([Total LOS]) which shows me the length of stay across the months however this shows a combination of two years worth of data across the same months.

I can add a list box to pick a specific year (DOA-Year) but ideally i'd like to show both years (2016 and 2017 from the DOA-Year dimension) on the same graph.

I'm sure its easy to do but my brains just not working this morning.

Would i do this with a 'calculated dimension' or somewhere as an expression?

Many thanks in advance.

[amit_saini]

Hi,

Create 2 Expression one for 16 and other of 17

(AVG{<Year='2016'>([Total LOS])

(AVG{<Year='2017'>([Total LOS])

Thanks,

AS

[tresB]

If you have date field, you could try like:

=MonthName([Date])

Else, you could try concatenating fields like:

=Year & ' - ' & Month

[peterderrington]

This feels like its almost it however is it not missing an } symbol?

Should the Year option not be DOA-Year?

Thanks for the speedy responce

[peterderrington]

Tried:

Avg({<[DOA-Year]='2016'>}([Total LOS]))

which it accepted as an OK Expression however it then just shows 'No data to display' in the graph.

[amit_saini]

(AVG{<DOA-Year='2016'>}([Total LOS])

Yes you are right

Regards,

AS

[peterderrington]

So any idea as to why i'm getting the 'No data to display' message then?

[amit_saini]

Can u please share some sample data and what desired output u are looking for/

Thanks,

AS

[peterderrington]

Attached are two pictures of the normal data, each with the list box selected for either 2016 or 2017

I have attached an example of the data.

The fields we're looking at are:

Total LOS - 1 till end of NSECH

DOA-Month

DOA-Year

I want a line graph as shown but with the two years shown in different colours on the same graph.

Thanks.

[sasikanth]

Add One more calculated dimension

If(DOA-Year>2015,DOA-Year)

OR

Add DOA-Year as a dimension and change exp to
AVG({<DOA-Year={'2016','2017'}>}([Total LOS])