Above function for current-4th week affecting by filter

[keerthanagowsi]

Hi everyone, 

I have a requirement to show data for latest 5 weeks. The data should be calculated as follows:

Eg: latest week = 24

Latest - 4th week = 20

So the requirement is 24th week = quantity of 24th week - quantity of 20th week.

Similarly I have to show for latest 5 weeks.

I created an expression as above({week} sum(quantity), 4)q is. This is working fine as long as I apply the filter. Once I apply the filter, my latest 5 weeks occurring correctly, but rest of the weeks are also coming in the view with some values. 

Please help me in finding the solution. 

Thanks in advance, 

Keerthana 

[keerthanagowsi]

No luck kush 😞 

It is giving me incorrect values. 

[keerthanagowsi]

Hi Jyothish,

I am having N number of records for each week and so I cannot use peek or previous functions.

So that thought of trying in expressions.

[Kushal_Chawda]

@keerthanagowsi  have you tried the updated expression?

[Kushal_Chawda]

It's working for me

=sum(aggr(sum(Qty)- above(  sum({<Week>}Qty),4),Week))*avg(1)

[sunny_talwar · Accepted solution]

May be sorting is not correct from the script... in which case may be this

=Sum(Aggr(Sum(Qty) - Above(Sum({<Week>} Qty), 4), (Week, (NUMERIC))))

[keerthanagowsi]

Not working for my huge data.. couldn't figure out the issue

[Kushal_Chawda]

can you share snapshot of actual data?

[keerthanagowsi]

It's working after adding Numeric. I was spending so much time for this.

Thank you so very much Sunny and Kush 🙂

Can anyone explain how this expression works? since am new to qliksense.

[sunny_talwar]

So, the expression is looking at Sum(Qty) for the current row and from it subtracting Sum(Row) from 4 rows (weeks because weeks in the dimension) above. The problem seems to be the fact that your Week isn't sorted in ascending order from the script. Because if it was then @Kushal_Chawda expression would have worked. All I proposed was to sort Week in ascending order within the Aggr() function so that 5 comes before 6 even if in the script 5 is loaded after 6, for example.

[keerthanagowsi]

Got it. Thanks again to you and @Kushal_Chawda