topic Set analysis expression...is this correct? in QlikView

Set analysis expression...is this correct?

salto — Mon, 09 Dec 2013 13:12:26 GMT

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?

Anonymous — Mon, 09 Dec 2013 13:34:48 GMT

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

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

Mon, 09 Dec 2013 13:38:56 GMT

post sample

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

salto — Mon, 09 Dec 2013 14:22:09 GMT

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?

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

Mon, 09 Dec 2013 14:44:41 GMT

Access denied i get

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

Anonymous — Mon, 09 Dec 2013 14:45:41 GMT

me too...

access denied...

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

saumyashah90 — Mon, 09 Dec 2013 15:00:08 GMT

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

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

Mon, 09 Dec 2013 15:22:12 GMT

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?

Anonymous — Mon, 09 Dec 2013 15:24:26 GMT

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

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

Mon, 09 Dec 2013 15:26:56 GMT

=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?

Mon, 09 Dec 2013 15:44:39 GMT

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