topic Re: rolling 6 weeks value in QlikView

rolling 6 weeks value

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

i have a chart which has dimension as cyclic group that includes Date, hour, month.

i need to calculate numbers of calls received to the corresponding dimension.

i also need to calculate rolling 6 weeks' average calls received based on date selection.

for example,if the date i have selected is monday, then i need to show last 6 monday's average.

Also, this average should only be enabled if the dimension is chosen as Hour.

How can i do this. Please help me.

Anonymous — Tue, 30 Aug 2016 12:55:10 GMT

Defining a Flag at ETL like:

If(weekday(Date)='Mon', 'M',

If(weekday(Date)='Tue', 'T',

and so on..)) as Flag

Then use these Flags value in set expression and something like mentioned in these thread?

Tue, 30 Aug 2016 13:48:53 GMT

is it possible to do in the table rather than in script?

vishsaggi — Tue, 30 Aug 2016 14:04:55 GMT

Do you have a sample app to share which would be easy to work on?

Tue, 30 Aug 2016 15:54:29 GMT

i m trying an expression like this

Sum({<date={">=$(=Weekstart(Max(date), -5))<=$(=Date(Max(date)))"}>} Actualcalls)

but i m not getting expected output.

i think there is problem in weekstart.

if i select the date as 'aug 7' i.e., sunday, then it should calculate last 6 sundays and aug 7.

how to do this. please help

vishsaggi — Tue, 30 Aug 2016 16:03:05 GMT

= Sum({< date= {"$(= '>=' & Weekstart(Max(date), -5)) and '<=' Date(Max(date)))" } >} Actualcalls)

can you try that ?

Wed, 31 Aug 2016 08:55:34 GMT

no Vish, this is not the expected output

vishsaggi — Wed, 31 Aug 2016 12:57:20 GMT

Can you share a sample app and expected output please if possible ?