Hi all,
in my query subject I have an element defined as follows:
cast(_days_between ( getdate();[Logic view].[My query subject].[LastPaymentDate])/365; int)
LastPaymentDate is defined as follows:
maximum[Logic view].[My query subject].[PaymentDate])
LastPaymentDate tells me the latest date of a payment, while the other element defines how many years are spent from the last payment.
These definitions work well but, when I use the filter pane of the query subject, the filter doesn't work and zero rows are returned >:(
Does anybody can help me to solve this issue?
Please!
Thanks in advance
Remember that a filter must return a boolean (true/false) value. The expressions you have listed are data items. What is your filter expression?
Quote from: Rosanero4Ever on 09 Oct 2012 09:35:45 AM
Hi all,
LastPaymentDate is defined as follows:
maximum[Logic view].[My query subject].[PaymentDate])
LastPaymentDate tells me the latest date of a payment
I would be careful in assuming this. The query context will determine the value of the aggregate. If you add , say, customer and year to the report query you will end up with the latest payment date for each set of [customer,year] by default.
The aggregate behavior can be manipulated by means of the regular aggregate property, you do not need to hardcode it !!
Quote from: bdbits on 09 Oct 2012 01:59:59 PM
Remember that a filter must return a boolean (true/false) value. The expressions you have listed are data items. What is your filter expression?
Right! In my filter I use a booelan expression on the element defined by
cast(_days_between ( getdate();[Logic view].[My query subject].[LastPaymentDate])/365; int)
Quote from: blom0344 on 09 Oct 2012 02:08:43 PM
The aggregate behavior can be manipulated by means of the regular aggregate property, you do not need to hardcode it !!
Thanks for your reply, but, sorry, I don't understand.
Can you explain better your advice?
Thanks a lot
Each data item in FM has a set of properties. One of them is the regular aggregate. You can set this to maximum by default instead of coding it as you did. The enables a report writer to change it should he wish it. The default is set to sum for usage type 'fact' ..
Thank you very much for your good explanation.
So, I'll try to remove maximum() function replacing it with the aggregation property of the element.
Regards.
ps
but....this will resolve the problem I wrote about the filter? ???
No, but bdbits already pointed out that there is a distinct difference between a data item expression and a filter expression. My input was concerned with you hard coding an aggregate. Perhaps some formal FM training would clear things up?
I know the difference between a data item expression and a filter expression, but avoiding hardcoding don't solve my filtering problem.
Please reread bdbits post.. ::)
Actually, the expression to determine the no. of years is wrong too. Dividing by 365 will not give accurate result because the leap year has an additional day. Moreover, that additional day is not equally divided among all 4 years so until the leap year is reached, it is dividing by a higher average.