Amount claculation in view structure
Hi!
we have an issue we experience in different cases that as much that i know we could not find a solution for it. In order to explain the issue I prepared an example, see tables attached.
in this hypothetical case the dim status table is connected to the status in status history table, and status history table is connected to payments table.
when asking a question withing the fields of payments alone like-
the amount sent to UK- it would work.
but if I'll ask the same question and put a user prompt filter of status description \ filtering a few statuses from dim statuses- the amount presented would be multiplied by the number of statuses the payment has in status history table.
same would happen even if we filter the date to - 20170702 and want to see the sum of the amount.
every question, not showing the data on a payment level (the payment is not in the columns\ rows) and uses any attribute out of Payments table- would mess up the sum \avg of the amounts.
is there any why to define the sum of the amount to be distinct per payment ID? or any other way that not requires always using appends in cases like these?
Thanks,
Tal
the are many other examples
The same question 
Hi Tal,
Just wanted to let you know that I am looking into this but may not have a substantive response for you until tomorrow morning!
Regards,
Nathan
Hi Tal,
Just wanted to let you know that I am looking into this but may not have a substantive response for you until tomorrow morning!
Regards,
Nathan
Hi Tal,
Hopefully I have understood your situation correctly here. It sounds like the join between the two tables causes problems for you. In this case adding the filter, will bring in the second table in the same way that bringing a field into the report itself would.
I am not sure what the appropriate fix is here, by my guess is that you will need to play with the join type/carnality until this works. The first thing worth looking into would be switching this from an inner join to outer join, as inner joins bring in all values from each table.
Have you tried playing with these at all?
Please let me know if I have mis-understood you here.
Regards,
Nathan
Hi Tal,
Hopefully I have understood your situation correctly here. It sounds like the join between the two tables causes problems for you. In this case adding the filter, will bring in the second table in the same way that bringing a field into the report itself would.
I am not sure what the appropriate fix is here, by my guess is that you will need to play with the join type/carnality until this works. The first thing worth looking into would be switching this from an inner join to outer join, as inner joins bring in all values from each table.
Have you tried playing with these at all?
Please let me know if I have mis-understood you here.
Regards,
Nathan
HI Nathan!
thanks for your answer.
I have to say I have been working on YF for 2 years now.
so I can defiantly say that this is not because of the JOIN. all our views are built with OUTER JOIN.
until today we managed with this issue- but we are modeling for a new view and I was hoping for a solution from the tool side, like a way to make sure sum would not be multiplied ....
Thanks,
Tal
HI Nathan!
thanks for your answer.
I have to say I have been working on YF for 2 years now.
so I can defiantly say that this is not because of the JOIN. all our views are built with OUTER JOIN.
until today we managed with this issue- but we are modeling for a new view and I was hoping for a solution from the tool side, like a way to make sure sum would not be multiplied ....
Thanks,
Tal
Hi Tal,
Apologies for the delay, I was out of town.
Unfortunately, it is difficult for me to offer a solution as this is an issue with your set-up specifically (we have many of clients who are able to create complex views without issues at the report level). I do trust that you know the program, but in the end Yellowfin is generating an SQL query, so if combining tables produces adverse results, there is no other culprit besides the join. (might be cardinality as well as join type)
I would suggest that you try to create your own query at the DB level to achieve the results you are seeking, and then compare this against what Yellowfin is generating.
I am sorry I cannot offer more here. If you are interested and can provide me access to a test environment with this issue, I can poke around for a while?
Regards,
Nathan
Hi Tal,
Apologies for the delay, I was out of town.
Unfortunately, it is difficult for me to offer a solution as this is an issue with your set-up specifically (we have many of clients who are able to create complex views without issues at the report level). I do trust that you know the program, but in the end Yellowfin is generating an SQL query, so if combining tables produces adverse results, there is no other culprit besides the join. (might be cardinality as well as join type)
I would suggest that you try to create your own query at the DB level to achieve the results you are seeking, and then compare this against what Yellowfin is generating.
I am sorry I cannot offer more here. If you are interested and can provide me access to a test environment with this issue, I can poke around for a while?
Regards,
Nathan
I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!
Regards,
Nathan
I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!
Regards,
Nathan
Replies have been locked on this page!