10 Replies Latest reply: Dec 9, 2013 10:44 AM by Thomas Jensen

# Set analysis expression...is this correct?

Hello,

In my task to calculate the overdue amounts of our customer's invoices and its point-in-time evolution, I have written two expressions.

One of them is the Total Balance: SUM of the amounts of the invoices issued: no problems here:

num(sum({<"Posting Date"={"<=\$(=date(Max(DateID),'DD/MM/YYYY'))"}>} "Amount"),'#.##0' & ' €')

But, if I want to SUM those amounts that belong to invoices not yet overdue (this is, the "DueDate" field is lowerthan the DateID field), this will always return ZERO:

=num(sum({<"DueDate"={">(=date(DateID,'DD/MM/YYYY'))"}, "Posting Date"={"<=\$(=date(Max(DateID),'DD/MM/YYYY'))"}>} "Amount"),'#.##0' & ' €')

Is this an error on my expression (note that I am not using Max(DateID)), or this simply cannot be accomplished?

Many thanks!

• ###### Re: Set analysis expression...is this correct?

Hi,

The way I see it, you are using fields on both the sides of a condition, which will not work as the set will not be able to match a series of records against another series of records. Note that in your earlier expression, you are using a field against a defined value, like max(date). So the set expression is able to validate each record of your field against one particular value.

Hope this helps.

Regards,

• ###### Re: Set analysis expression...is this correct?

post sample

• ###### Re: Re: Set analysis expression...is this correct?

I am posting a sample, hope it is clear. The chart in the left bottom part is correct only for the "Total Balance" column, because it takes the Max(DateID) field value and not the DateID itself.

So, could I write the set analysis expression in some other way to fulfill my requirements?

• ###### Re: Set analysis expression...is this correct?

me too...

• ###### Re: Set analysis expression...is this correct?

Hmm first of all i would suggest you not to use different date formats and remember to correct the main settings.

To get your initial date you need to this:

=Date(Date#(Max(DateID),'YYYYMMDD'),'DD/MM/YYYY') : This will get ou the max dateID

a little tip on debugging:

Delete your label in your straight tabel - Then you can see which value the expression is actually getting, and hereby you can start debugging on which part is not correct

• ###### Re: Re: Re: Re: Set analysis expression...is this correct?

Please check attached. I have used the same condition in the script and have created a flag field.

And used that in your set statement.

hope this helps.

Regards,

• ###### Re: Re: Re: Re: Set analysis expression...is this correct?

=num(sum({<[Initial Entry Due Date]={"<=\$(=Date(Date#(Max(DateID),'YYYYMMDD'),'DD/MM/YYYY'))>=\$(=Date(Date#(Max(DateID),'YYYYMMDD'),'DD/MM/YYYY'))"},[Posting Date]={"<=\$(=date(Max(DateID),'DD/MM/YYYY'))"}>} "Amount"), '#.##0 €')

Replace Max fucntion with whatever dates you want.

sorry shouldnt have been a post to you

• ###### Re: Re: Re: Re: Re: Set analysis expression...is this correct?

try something like this: its not quite correct i can see - but i hope it can help you some way

• ###### Re: Set analysis expression...is this correct?

Hi Salto,

Sorry for Going Off Track

Can you please think for this