topic Excluding the remaning weeksof the year from an expression in QlikView

Excluding the remaning weeksof the year from an expression

Tue, 20 Oct 2015 13:09:26 GMT

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!

Re: Excluding the remaning weeksof the year from an expression

Gysbert_Wassenaar — Fri, 23 Oct 2015 12:14:54 GMT

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

Re: Excluding the remaning weeksof the year from an expression

rubenmarin — Fri, 23 Oct 2015 12:25:28 GMT

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...