Set analysis expression...is this correct?

[salto]

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!

[Anonymous]

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,
Aadil

[#3]

post sample

[salto]

Thanks for your early answer.

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?

[#5]

Access denied i get

[Anonymous]

me too...

access denied...

[saumyashah90]

Hi Salto,

Sorry for Going Off Track

Can you please think for this

http://community.qlik.com/thread/101146?start=0&tstart=0

and\

http://community.qlik.com/thread/101325

[#8]

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

[Anonymous]

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,

Aadil

[#10]

=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