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 

[Kushal_Chawda]

try below

sum(aggr(above( sum({week}quantity), 4),week))*avg(1)

[keerthanagowsi]

Hi Kush,

Thank you for the reply. But this is not working for me. Instead it is returning 0 for all the records.

[dieterwoestemeier]

Maybe something like this?

Last 4 weeks based on the Today() function.

Sum({<Week=,Week={">=$(=Week(Today())-4)<=$(=Week(Today()))"}>} quantity)

[keerthanagowsi]

Thank you for the reply, but this is also not working. I cannot use today, because my latest week and current week will not be the same. So this is the set I used

above(Sum({<[Week]=,[Week]={"=[Week]>=$(=Max(total[Week],5))"}>} Quantity),4)

It cannot do the lookup and bring the 4th week value from the current. Instead it is returning null for me.

[Kushal_Chawda]

Would be able to share sample?

[keerthanagowsi]

Please find the sample:

Data : 

Requirement:

[Kushal_Chawda]

try below

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

[jyothish8807]

Hi ,

Try like this:

[Table]:
Load *,
previous(previous(previous(previous(Qty)))) as Qty2;
LOAD * INLINE
[
Week,Qty
1,10
2,40
3,50
4,15
5,25
6,35
7,65
8,20
9,30
10,10
](delimiter is ',');

Then in chart simply do Qty-Qty2 and limit dimension to latest 5 week.

Br, 

KC

[Kushal_Chawda]

Check the updated expression