14 Replies Latest reply: Feb 27, 2014 11:09 AM by Ella Miller

# set analysis: sum of amount when date in range not working

Hi Guys,

I'm trying to use set analysis and get the sum of a field called "count_recommendations" when its date is in range.

I'm using the following:

sum({<DateOnly= {'<=\$(=vYesterday) >=\$(=v3days)'}> } count_recommendation)

but the results show only 0.

vYesterday and v3days are variables that are set to recieve yesterday and 3 days ago dates respectively.

Am I using the wrong syntax? because an other question here with that topic was resolved with that answer....

can you help?

• ###### Re: set analysis: sum of amount when date in range not working

Hi,

Try this:

Sum({\$<[DateOnly] = {'\$(vYesterday), \$(v3days)'}>} count_recommendation)

if doesn't work

Maybe try this:

Sum({\$<DateOnly = {\$(vYesterday), \$(v3days)}>} count_recommendation

you can try this with the values of the variable Sum({\$<DateOnly = {'value1','value2')}>} count_recommendation

Cy

• ###### Re: set analysis: sum of amount when date in range not working

Doesn't work :\

Now it shows "-" instead of 0.

• ###### Re: set analysis: sum of amount when date in range not working

Hey Elula,

Can you put the two variables in a text box and see if you are getting expected results. You can try "  instead of ' .

• ###### Re: set analysis: sum of amount when date in range not working

Hi,

try double Quotes(") insted of single quotes(') this.

• ###### Re: set analysis: sum of amount when date in range not working

Not working...

I still get 0 when using " instead of ' ...

• ###### Re: set analysis: sum of amount when date in range not working

What is vYesterday and v3days? i.e. what output they are giving.

What is the formatting of DateOnly?

• ###### Re: set analysis: sum of amount when date in range not working

they are showing the date as a number...

when I put them in list box I see:

vYesterday=41696

v3days= 41694

when using the expression in an other list box:

=if (DateOnly>=\$(v3days),

if (DateOnly<=\$(vYesterday), DateOnly))

I get: 41694, 41695, 49696

So you see they are all of the same format...

• ###### Re: set analysis: sum of amount when date in range not working

Looks like your problem is non matching format..

You can use something like below..

sum({<DateOnly= {'<=\$(=Date(vYesterday),'DD/MM/YYYY') >=\$(=Date(v3days),'DD/MM/YYYY')'}> } count_recommendation)

DD/MM/YYYY need to be changed as per your Date format...

• ###### Re: set analysis: sum of amount when date in range not working

I have attached an example with exactly what you are trying to do. First take a look at the variables then the expression

Hope it helps.

Thanks

AJ

• ###### Re: set analysis: sum of amount when date in range not working

FYI,

When you define the variables in Variables overview you have to use =

Eg.

vYes

=Today()-1

• ###### Re: set analysis: sum of amount when date in range not working

That seem to be my Problem!!

Now it seem to work...

• ###### Re: set analysis: sum of amount when date in range not working

Hi,

try to set the number format of variables as date in the document properties in the tab variables.

• ###### Re: set analysis: sum of amount when date in range not working

The expression itself looks fine.  The problem is likely with the date format.  Variables must have the same format as the field Date.  Or formatted directly in the set expression, maybe:
sum({<DateOnly= {'<=\$(=date(vYesterday)) >=\$(=date(v3days))'}> } count_recommendation)

It would be easy to resolve if you upload an example

• ###### Re: set analysis: sum of amount when date in range not working

Thank you all so much for you help!

the issue is now fixed...my expression was correct there was a problem in the variable...