topic Calculated Dimension? in QlikView

Calculated Dimension?

peterderrington — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculated Dimension?

amit_saini — Fri, 05 Jan 2018 10:22:41 GMT

Hi,

Create 2 Expression one for 16 and other of 17

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

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

Thanks,

Re: Calculated Dimension?

tresB — Fri, 05 Jan 2018 10:23:26 GMT

If you have date field, you could try like:

=MonthName([Date])

Else, you could try concatenating fields like:

=Year & ' - ' & Month

Re: Calculated Dimension?

peterderrington — Fri, 05 Jan 2018 10:38:31 GMT

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

Re: Calculated Dimension?

peterderrington — Fri, 05 Jan 2018 10:41:35 GMT

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.

Re: Calculated Dimension?

amit_saini — Fri, 05 Jan 2018 10:42:10 GMT

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

Yes you are right

Regards,

Re: Calculated Dimension?

peterderrington — Fri, 05 Jan 2018 10:46:25 GMT

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

Re: Calculated Dimension?

amit_saini — Fri, 05 Jan 2018 10:48:05 GMT

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

Thanks,

Re: Calculated Dimension?

peterderrington — Fri, 05 Jan 2018 11:04:32 GMT

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.

Re: Calculated Dimension?

sasikanth — Fri, 05 Jan 2018 11:05:53 GMT

Add One more calculated dimension

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

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

Re: Calculated Dimension?

Anonymous — Fri, 05 Jan 2018 11:12:15 GMT

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

Re: Calculated Dimension?

peterderrington — Fri, 05 Jan 2018 11:16:55 GMT

Brilliant!

Thank you. Its all showing exactly as i needed it.

Many thanks.