Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
salto
Valued Contributor II

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!

10 Replies
aadilmadarveet
Valued Contributor

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

Not applicable

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

post sample

salto
Valued Contributor II

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

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?

Not applicable

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

Access denied i get

aadilmadarveet
Valued Contributor

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

me too...

access denied...

saumyashah90
Valued Contributor

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

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

Not applicable

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

aadilmadarveet
Valued Contributor

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,

Aadil

Not applicable

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