Excluding the remaning weeksof the year from an expression

[#1]

Hi,

I'm having trouble with calculating the weekly cumulative revenue. Im currently using below expression:

= if(sum({<KA_SUJaar={$(=year(today()))}, table_code = {$(#vOEI_of_AEI)}>}revenue)=0,0,  // To exclude the remaining week of the year

(sum({<KA_SUJaar={$(=year(today()))}, table_code = {$(#vOEI_of_AEI)}>}revenue))  
-
(sum({<KA_SUJaar={$(=year(today())-1)}, table_code = {$(#vOEI_of_AEI)} >}revenue))
+
if(rowno()=1,0,above([Cum. verschil t.o.v. VJ])))

The if statement is to make sure the expression results in a zero when the selected week is in the future. See image. (red bars)

However, if a weekly revenue in between is zero, the expression doesnt work anymore! Does anyone know a way to change the expression and make it work correctly?

Any help is appriciated!

[Gysbert_Wassenaar]

Can you post a small example qlikview document that demonstrates the issue?

[rubenmarin]

Hi Aart, if weeks are related to a date field maybe this works:

= if(Max(dateField)>ReloadDate(),0, // Or Min(DateField), depens if you want to count current period

(sum({<KA_SUJaar={$(=year(today()))}, table_code = {$(#vOEI_of_AEI)}>}revenue))  
-
(sum({<KA_SUJaar={$(=year(today())-1)}, table_code = {$(#vOEI_of_AEI)} >}revenue))
+
if(rowno()=1,0,above([Cum. verschil t.o.v. VJ])))

Also you can check directly the week, but i saw is a cycling group a usind date can work with weeks, months, years...