Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Date Expression

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.


qlikexample.png

Tags (1)
1 Solution

Accepted Solutions

Re: Date Expression

Like this?

Capture.PNG

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

5 Replies

Re: Date Expression

In a pivot table, you can try this

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

Partner
Partner

Re: Date Expression

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

   

Category tickets countMonthYearTickets
SimpleJan'1632
MediumJan'1622
ComplexJan'1621
SimpleFeb'1625
MediumFeb'1624
ComplexFeb'1620

Re: Date Expression

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

Re: Date Expression

Like this?

Capture.PNG

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

Partner
Partner

Re: Date Expression

Bingo, thanks Sunny!