Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Partner
Partner

Calculate limited average for field's values

Hello,

I am looking for non-trivial solution for my task:

I do have data table that contains some values (people's daily counts) for multiple locations over time (for ~3 years)

Screen Shot 2016-08-19 at 5.02.50 PM.png

I need to calculate (for each day) the Average Traffic 10 days prior and 10 days after the particular date.

It's easy to do in the chart:

rangeavg(

  below(sum([Traffic Enters]), 0, 10),

  above(sum([Traffic Enters]), 1, 10)

)


But I need to do it in the script...


Yes, there is a way using GROUP BY, FOR TO - NEXT, PEEK, etc approach...

But I was hoping somebody could suggest a better and more elegant solution...


Looking forward to your suggestions!


Regards,

Vlad


3 Replies
MVP & Luminary
MVP & Luminary

Re: Calculate limited average for field's values

I think rather not. You will quite probably need a group by to get the sum per day and those result then matching with one or two peek-loadings (with different sortings to get the previous records from both sites) and afterwards a further aggregation to get the average.

Alternative to the mentioned peek-runs could I think on a kind of As-of-Table within the script (each day is there matched with the 10 days prior and after it) and which will be taken to flag the right days together. A different way might be to match the days simply to their RowNo in 20-buckets (but it will be difficult if there gaps between them).

In conclusion I think there would be alternatives possible but I'm not sure if they would be more elegant or better perform than the "classical" way.

- Marcus

Re: Calculate limited average for field's values

Expecting O/P from above Data?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Calculate limited average for field's values

Anil,

Not sure I've got your question..

VK