topic Re: Date Expression in QlikView

Date Expression

nate_eyster — Wed, 29 Mar 2017 20:02:38 GMT

Stuck on an expression. Use case below.

ex: suppose if I select month July'16 in list box it should give me average by taking the previous two months and the present month selected as well. I want this to be dynamic across all months. I want to achieve this in Qlikview as a table and also as line graph.

Average of Category tickets count = (present month + past 2 months)/3. If you go to cell 'K11' for 2nd table you will be able to understand what I am trying to explain, if I am not clear.

Example as per Excel sheet:

July'16 Average ticket count: = (I3+J3+K3)/3 = 21

I tried with set expressions and other workarounds but failed to achieve it.

Re: Date Expression

sunny_talwar — Wed, 29 Mar 2017 21:04:21 GMT

In a pivot table, you can try this

RangeAvg(Before(Sum(Value), 0, 3))

Re: Date Expression

nate_eyster — Wed, 29 Mar 2017 21:22:08 GMT

Still can't wrap my head around this.. rearranged data in this format..

Category tickets count	MonthYear	Tickets
Simple	Jan'16	32
Medium	Jan'16	22
Complex	Jan'16	21
Simple	Feb'16	25
Medium	Feb'16	24
Complex	Feb'16	20

Re: Date Expression

sunny_talwar — Thu, 30 Mar 2017 08:00:46 GMT

Not sure what you mean, did the above expression not work for you in a pivot table?

Re: Date Expression

sunny_talwar — Thu, 30 Mar 2017 08:13:29 GMT

Like this?

=If(ColumnNo() > 2, RangeAvg(Before(Sum({<MonthYear>}Tickets), 0, 3))) * Avg(1)

Re: Date Expression

nate_eyster — Thu, 30 Mar 2017 12:45:59 GMT

Bingo, thanks Sunny!