Compare current sum Rev VS last year sum Rev

[wassimharfoush]

I am trying to compare the sum of Revenue for period based on current selection of a DateField VS The same period for

previous year and I am using the expression for previous year period:

Sum({<Datefield={'$(=addyears(DateField-1))'}>}Rev)

That works when the user select a single period but doesn't work when the user select multi period.

[alec1982]

it is a date field with format as 09/01/2014 and so on back to 3 years

[alec1982]

to simplify the issue and understand what's wrong I used the following expression:

=sum( {$<RDATEValue={"$(=(AddMonths(min([RDATEValue]),-12)))"}>} REV)

Which returns the rev of the minimum period and it works .

Now when I add the greater than symbol ">" it returns 0.

=sum( {$<RDATEValue={"$(>=(AddMonths(min([RDATEValue]),-12)))"}>} REV)

Hope someone can help

[alec1982]

Hi guys,

the following made it for me using Min and Max

num(sum( {$<RDATEValue={">=$(=(AddMonths(min([RDATEValue]),-12))) <=$(=(AddMonths(max([RDATEValue]),-12)))"}>} REV))

but what if the user makes few selections on the RDATEValue field and they are not in order?

meaning what if the user select 09/01/2014 and 06/01/2014 and 01/01/2014 We need to get the REV for the same periods from the previous year..

Let me know your thoughts..

[ashishkalia]

hies alec1982,

Greetings.

My suggestion is that let the user do any selections on date field but for your computations, calculate it on the variables
eg. user selects 20/8/2014 as the max date for period
so go to the variable and calculate it as vMaxPeriodDate=addmonths(max(RDATEvalue),-12)
do same with the min period date
and then introduce it in your expression as

=sum( {<RDATEValue={'<=$(vMaxPeriodDate)'},RDATEValue={'<=$(vMinPeriodDate)'}>} REV);
                 

[alec1982]

Thank you for the update but your input doesn't fix the issue I am having.

The issue is using the following expression

num(sum( {$<RDATEValue={">=$(=(AddMonths(min([RDATEValue]),-12))) <=$(=(AddMonths(max([RDATEValue]),-12)))"}>} REV))

The user will get the REV for all periods in between the min and max values he selects.

What if the user select three dates as follow 09/01/2013, 07/01/2013 and 01/01/2014 the result I am looking for is the REV for only the three periods for the previous year while the expression that i have and the one that you provided return the REV for all periods between Min and Max dates and not only the three periods the user selected.

Best,

Alec

[alec1982]

Wondering if anyone can help?

Best,

Alec

[muniyandi]

Hi ,

if possible ,Can you post sample file.

[alec1982]

Here we go .. I have attached a sample

[alec1982]

Hi Mathew,

Have you had a chance to look at the sample?

Best,

Alec

[muniyandi]

Hi Alec,

Kindly verify my attachment file. Check Variable test name

i hope this will help to u.

Thanks,

Muni !