To calculate the sum over 3M, 6M and 12 M

[#1]

Hello Friends,

I have been trying to calculate the sum using the below expression :-

3M Stock =sum(if([Latest Date Received] <= today()-90 ,[Depot Stock Euro (Item cost)]))

6M Stock=sum(if([Latest Date Received] <= today()-180,[Depot Stock Euro (Item cost)]))

12M Stock = sum(if([Latest Date Received] <= today()-365,[Depot Stock Euro (Item cost)]))

However, even after applying the condition to calculate only within the period of 3M(if([Latest Date Received] <= today()-90), I do get a calculated sum for complete 12 months.

Can anyone advice ?

Thanks !

[Anonymous]

Hi!
Try setting up the data function. Something like this:

3M Stock =sum(if([Latest Date Received] <= date(today()-90) ,[Depot Stock Euro (Item cost)]))

6M Stock=sum(if([Latest Date Received] <= date(today()-180),[Depot Stock Euro (Item cost)]))

12M Stock = sum(if([Latest Date Received] <= date(today()-365) ,[Depot Stock Euro (Item cost)]))

[stabben23]

Build 3 variables in script that holds each of the month calculations.

Let v12 M =date(addmonths([Latest Date Received],-12)) ;

Let v6 M =date(addmonths([Latest Date Received],-6)) ;

Let v3 M =date(addmonths([Latest Date Received],-3)) ;

then in Your Expression sum({$<[Latest Date Received] ={">=($(v12M))"}>}[Depot Stock Euro (Item cost)])

[#4]

HI,

Thanks for your help but it does not work.

[stabben23]

Hi,

some typos, try sum({$<[Latest Date Received] ={">=($(=v12M))"}>}[Depot Stock Euro (Item cost)])

[#6]

No, this does not work either

[stabben23]

Sorry, you should ofcourse build the variables inside app, not in script.

[Jason_Michaelides]

Your basic problem is (I think) you are using <= instead of >=. However yours is still an inefficient way of doing this. Is [Latest Date Received] a row-level value? i.e. does every row in the fact table have a value for this?  Assuming it is and also assuming you can reload the script each day I would do it like this:

In script:

LOAD

     <other fields>,

     If([Latest Date Received] >= AddMonths(Today(),-3),'3M'

          ,If([Latest Date Received] >= AddMonths(Today(),-6),'6M'

               ,If([Latest Date Received] >= AddMonths(Today(),-12),'12M'

               ,'Other')))

                                        AS     %Flag_Age

FROM ....;

In UI:

3M Stock = Sum({<%Flag_Age={'3M'}>} [Depot Stock Euro (Item cost)])

6M Stock = Sum({<%Flag_Age={'6M'}>} [Depot Stock Euro (Item cost)])

12M Stock = Sum({<%Flag_Age={'12M'}>} [Depot Stock Euro (Item cost)])

Other Stock = Sum({<%Flag_Age={'Other'}>} [Depot Stock Euro (Item cost)])

Hope this helps,

Jason