topic Sum on Set Analyses question in QlikView

Sum on Set Analyses question

andre_ficken — Thu, 31 Mar 2011 11:14:28 GMT

Hello, I have the following question: I am reporting on a table with open invoices the invoice has a GL bookingdate. I also have a table with all dates in it. What I would like to do is a historical open invoice report using the following:

sum(invoiceamount) but only when isnull(getfieldselections(bookingdate)) or bookingdate <= getfieldselections(bookingdate).

The latter 2 condtions I would like to wrap in a set analyses. Is this possible? I have been testing and trying for a while now, without much luck sofar. Any help would be appriciated.

Txs,André

Sum on Set Analyses question

Anonymous — Thu, 31 Mar 2011 11:36:41 GMT

don't know for sure ... but something like this might be in the right direction?

sum( {< bookingdate = {'<=$(getfieldselections(bookingdate))' ,$(nullvalue()) } >} invoiceamount)

Sum on Set Analyses question

andre_ficken — Thu, 31 Mar 2011 11:43:55 GMT

I have some additional info: I tried to get this using an if in the expression.
For the amounts that worked fine, the only thing I noticed that the subtotal is not working anymore and displays a '-'.

Sum on Set Analyses question

Anonymous — Thu, 31 Mar 2011 12:11:15 GMT

[quote user="André ***"]subtotal is not working anymore and displays a '-'

I did have the same problem before ... this has to do with combined expressions, in this case the subtotal has no clue what to do.

did you try something like the following (the if you talked about):

sum( if ( isnull ( getfieldselections(bookingdate) ) or bookingdate <= getfieldselections(bookingdate) , invoiceamount )

Sum on Set Analyses question

andre_ficken — Thu, 31 Mar 2011 12:15:13 GMT

It is definitely more complex. The set analyses does not work. Besides that I need to get a set for the invoiceamounts based on invoice date, a set for the invoice payments based on the payment date and finally subtract the 2. So it cannot contain null values, otherwise the result is null and the totals do not work. It is essential that the line and column total calculations work correctly.

Sum on Set Analyses question

Anonymous — Thu, 31 Mar 2011 12:18:28 GMT

Maybe a sample might help for the understanding of the problem...

Sum on Set Analyses question

andre_ficken — Thu, 31 Mar 2011 13:13:41 GMT

I cannot post any data here, since it is company confidential. Mail me (andre.***@viafi.com) your direct address so I can mail a sample to you directly outside this forum. Txs,André

Sum on Set Analyses question

Anonymous — Thu, 31 Mar 2011 13:21:10 GMT

André,

You could also try to give a sample with 'other' data ... just to emphasis what the problem is.
Or try to explain with some table samples what you have and what the data should deliver.

rgrds,

Anita

Sum on Set Analyses question

andre_ficken — Thu, 31 Mar 2011 14:03:01 GMT

I have created a sample file with some explanation on screen. Hopefully you can help me out here.

Txs,André

Sum on Set Analyses question

Anonymous — Thu, 31 Mar 2011 14:32:08 GMT

As mentioned in one of the prior suggestions the SUM should be taken out of the IF structure.

I added the correct sum in the sample.


SUM(
 if ( isnull ( getfieldselections( [Boekdatum (K)] ) )
 or [Factuur Boekdatum] <= getfieldselections([Boekdatum (K)] )
 , Factuurbedrag, 0 )
)

instead of


if ( isnull ( getfieldselections( [Boekdatum (K)] ) )
or [Factuur Boekdatum] <= getfieldselections([Boekdatum (K)] )
, sum(Factuurbedrag), sum(0) )

rgrds,

Anita

Sum on Set Analyses question

andre_ficken — Fri, 01 Apr 2011 13:39:48 GMT

I am sorry, but after copying your solution I got extreme numbers. Even after isolating the invoice table I got the same results. One of the original invoices was for EUR 20.483,20. In the changed column with the formula it now shows as EUR 22.449.587,20. The same happens to the other 3 invoices.

I do not think that this will be a solution that is going to work. It also takes approx 40 seconds to calculate totals and we are only talking about a table wih a little over 140000 rows.

Any other suggestions?

Sum on Set Analyses question

andre_ficken — Sat, 02 Apr 2011 20:01:58 GMT

I did find some problems in the construction of the data. I have been working on this a bit more and I now believe that the proposed solution might work. I will be continuing work on tuesday.

Regs,André

Sum on Set Analyses question

Anonymous — Mon, 04 Apr 2011 08:15:52 GMT

The sample was just with a minimum of data without other strange calculation ...

But the idea is to do the if construction within the sum, this way the totals can be calculated by the graph table.

Got it working yet?

Sum on Set Analyses question

andre_ficken — Tue, 05 Apr 2011 06:56:08 GMT

I have added a new test example with more data. I have also selected the specific customer of the previous example. You can see there that the total open amount is much more then just the 3 open invoices. I am not sure what is going on. Txs,André

Sum on Set Analyses question

andre_ficken — Tue, 05 Apr 2011 07:33:43 GMT

What I am trying to achieve with this is a flex debtors aging report. So, if the user does not select a date in the most left date selection, it should show all current open invoices. As soon as the user selects a date, the report should figure out which invoices were open at the time of that date. So the payment should not have been done yet (DBetaaldatum < selected date) and the amount paid should be less than the invoice amount.

In the other graphs it should only show data that is applicable to the selections applied. (invoices and payments). I was thinking of building set analyses sums for this, but looking at the selection criteria that need to be worked with I am not sure that that would be possible.