I need support to tweak a custom expression that should cumulate Sales values only until current week displaying the target line intact until last week of the quarter.

[Samit]

Here is the expression that I am working with:

Sum(if([Cal Week (Sales)]<='${Snapshotweek1}',[Sales Net Net USD CKF])) then Sum([Value]) OVER (AllPrevious([Axis.X])) AS [Cummulative Sales Net Net USD]

Issue is it is showing cumulative values even for the future weeks (it should show until week 202428 only). Current week is defined by the document property parameter ${Snapshotweek1}.

I am using external database connection type here. Please advise how can I edit this expression in order to limit displaying cumulative value until snapshot week 202428 only and target line until 202439 as is.

 

[Olivier Keugue Tadaa]

Hi Samit

I suspect this issue is related to the usage of the "THEN" expression. Usually THEN is used to optimize the query sent to the database and therefore, in your case, might lead to some side effects. especially when your data source is not in-memory but external.

Can you try this one instead? 

new expression. ::::: Sum(if([Cal Week (Sales)]<='${Snapshotweek1}',[Sales Net Net USD CKF]))  OVER (AllPrevious([Axis.X])) AS [Cummulative Sales Net Net USD]

Let me know if this worked

[Vanessa Virginia Sucre Gonzalez]

Hi Samit,

You could try something like this :

Max(case  when [Cal Week (Sales)]<='${Snapshotweek1}'  then Sum([Sales Net Net USD CKF]) OVER (AllPrevious([Axis.X])) else null end)

This will set to null all values after Snapshotweek1 and keep those prior to Snapshotweek1

Let me know if it works for you :) 

[Samit]

52 minutes ago, Olivier Keugue Tadaa said:

Hi Samit

I suspect this issue is related to the usage of the "THEN" expression. Usually THEN is used to optimize the query sent to the database and therefore, in your case, might lead to some side effects. especially when your data source is not in-memory but external.

Can you try this one instead? 

new expression. ::::: Sum(if([Cal Week (Sales)]<='${Snapshotweek1}',[Sales Net Net USD CKF]))  OVER (AllPrevious([Axis.X])) AS [Cummulative Sales Net Net USD]

Let me know if this worked

Hi Olivier, no it says the error: Could not find function 'AllPrevious'. This error can only be removed by adding THEN keyword with Sum([Value]) expression.

[Samit]

37 minutes ago, Vanessa Virginia Sucre Gonzalez said:

Hi Samit,

You could try something like this :

Max(case  when [Cal Week (Sales)]<='${Snapshotweek1}'  then Sum([Sales Net Net USD CKF]) OVER (AllPrevious([Axis.X])) else null end)

This will set to null all values after Snapshotweek1 and keep those prior to Snapshotweek1

Let me know if it works for you :) 

Hi Vanessa, error: Could not find function 'AllPrevious'.

[Vanessa Virginia Sucre Gonzalez]

2 hours ago, Samit said:

Hi Vanessa, error: Could not find function 'AllPrevious'.

Hi Samit, try this one:

Sum([Sales Net Net USD CKF]) THEN If([[X.Cal Week (Sales)]<'${Snapshotweek1}',Sum([Value]) OVER (AllPrevious([Axis.X])),null)

[Vanessa Virginia Sucre Gonzalez]

24 minutes ago, Vanessa Virginia Sucre Gonzalez said:

Hi Samit, try this one:

Sum([Sales Net Net USD CKF]) THEN If([[X.Cal Week (Sales)]<'${Snapshotweek1}',Sum([Value]) OVER (AllPrevious([Axis.X])),null)

@Samit Let me know if it works for you

[Samit]

19 hours ago, Vanessa Virginia Sucre Gonzalez said:

@Samit Let me know if it works for you

Terrific! It worked Vanessa with tiny tweak (as I alias X axis as Week). Here is the final expression: Sum([Sales Net Net USD CKF]) THEN If([X.Week]<='${Snapshotweek1}',Sum([Value]) OVER (AllPrevious([Axis.X])),null)

Thank you so much Vanessa for quick guidance!