Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Above function for current-4th week affecting by filter

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.

Keerthana

1 Solution

Accepted Solutions
MVP

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))))``

19 Replies

try below

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

Contributor III
Author

Hi Kush,

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

Contributor II

Maybe something like this?

Last 4 weeks based on the Today() function.

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

Contributor III
Author

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.

Would be able to share sample?

Contributor III
Author

Data :

Requirement:

try below

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

Master II

Hi ,

Try like this:

[Table]:
previous(previous(previous(previous(Qty)))) as Qty2;
[
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

Best Regards,
KC

Check the updated expression