SQL group the metrics
15 September, 2015
Hi,
I just found the SQL will group by metrics like 'duration/4'. But I don't want it to be grouped, The only thing need to be grouped by is the dimension: client name'
I added SUM function to ' Duration in hours ' just to avoid it being grouped by. However, when I apply sum to the calculated field ' average length of service per week ', it failed in giving the right outcome.
Thanks!
Cathy
I just found the SQL will group by metrics like 'duration/4'. But I don't want it to be grouped, The only thing need to be grouped by is the dimension: client name'
I added SUM function to ' Duration in hours ' just to avoid it being grouped by. However, when I apply sum to the calculated field ' average length of service per week ', it failed in giving the right outcome.
Thanks!
Cathy
Hi Cathy,
Sorry for the delay.
Looking at this It sounds like a bug... Are you please able to resubmit this as a ticket by emailing us at support(AT)yellowfin.bi so that we can associate this with the appropriate organization and get this looked into by our dev team for you?
Please let me know if you have any questions or concerns.
Kind Regards,
Cadell.
Sorry for the delay.
Looking at this It sounds like a bug... Are you please able to resubmit this as a ticket by emailing us at support(AT)yellowfin.bi so that we can associate this with the appropriate organization and get this looked into by our dev team for you?
Please let me know if you have any questions or concerns.
Kind Regards,
Cadell.
Sure. But do you know the email of support(AT)yellowfin? We are actually with Credible (partner of Yellowfin)
Thanks!
Cathy
Thanks!
Cathy
Hi Cathy,
The email for support is support@yellowfin.bi
Thank you,
Kyle
The email for support is support@yellowfin.bi
Thank you,
Kyle
Hi Cathy,
Using (AT) instead of the @ symbol when posting in public forums helps to deter bad robot's from picking up email addresses that they can then spam, but still allows humans such as yourself to read the email address. :-)
Sorry for the confusion and we will keep an eye out for your email.
Cheers,
Cadell.
Using (AT) instead of the @ symbol when posting in public forums helps to deter bad robot's from picking up email addresses that they can then spam, but still allows humans such as yourself to read the email address. :-)
Sorry for the confusion and we will keep an eye out for your email.
Cheers,
Cadell.
Dear Cadell,
do you have any update on this issue?
We are facing a similar one. We are on version 7.2 (Built: 20160331), and we are trying to build an invoice aging report of the following format:
, where Customer X are all the invoiced customers and under the defined buckets the summarized amount of all delayed invoices will be presented.
The source of the report is the �Invoices� table that has the following structure:
In this view, a date parameter has been defined (that is filled by prompt), in order to provide reporting results dependent on key date.
The way to reach from our source data to the reporting set was to calculate the days in delay per each invoice (User-defined_key_date minus Due_date), and based on this figure to fill in two (in our case) metrics with the invoice amount, when applicable. All these have been performed via calculated fields on view level, that finally looked like:
The formula for the first of the two buckets looks like this:
CASE WHEN ( ( Reporting Date - Due Date + 1 ) > 0 ) AND ( ( Reporting Date - Due Date + 1 ) <= 30 ) THEN SUM ( Invoice Amount in OC ) ELSE 0 END
Having performed all these steps, it sounded really easy just to create a report with field �Customer name� and the two bucket amounts (�1-30 days amount�, �> 30 days amount�), waiting for the metrics to be aggregated with SUM(). But this is not the case.
First of all, the Aggregation option in the report area for these calculated metrics is not available, nor a default aggregation can be defined on view level:
(as opposed to the Yellowfin�s consultants� response in the following posts:
yellowfin
yellowfin
Secondly, any attempt to create a new calculated field just to apply ?() function into these two bucket calculated fields ends with the error �aggregate function calls cannot be nested�.
Similarly to this post, the SQL generated groups by metric '2016-05-24' - "Invoices.csv"."f60485duedate" + 1
, whereas the only group by that we would like is per customer name.
Could you please help up with the solution of the dead-end that we are facing?
Aging report (that we are trying to create) is a very standard one in a variety of businesses and is considered as a must-have by our client.
With regards
do you have any update on this issue?
We are facing a similar one. We are on version 7.2 (Built: 20160331), and we are trying to build an invoice aging report of the following format:
, where Customer X are all the invoiced customers and under the defined buckets the summarized amount of all delayed invoices will be presented.
The source of the report is the �Invoices� table that has the following structure:
In this view, a date parameter has been defined (that is filled by prompt), in order to provide reporting results dependent on key date.
The way to reach from our source data to the reporting set was to calculate the days in delay per each invoice (User-defined_key_date minus Due_date), and based on this figure to fill in two (in our case) metrics with the invoice amount, when applicable. All these have been performed via calculated fields on view level, that finally looked like:
The formula for the first of the two buckets looks like this:
CASE WHEN ( ( Reporting Date - Due Date + 1 ) > 0 ) AND ( ( Reporting Date - Due Date + 1 ) <= 30 ) THEN SUM ( Invoice Amount in OC ) ELSE 0 END
Having performed all these steps, it sounded really easy just to create a report with field �Customer name� and the two bucket amounts (�1-30 days amount�, �> 30 days amount�), waiting for the metrics to be aggregated with SUM(). But this is not the case.
First of all, the Aggregation option in the report area for these calculated metrics is not available, nor a default aggregation can be defined on view level:
(as opposed to the Yellowfin�s consultants� response in the following posts:
yellowfin
yellowfin
Secondly, any attempt to create a new calculated field just to apply ?() function into these two bucket calculated fields ends with the error �aggregate function calls cannot be nested�.
Similarly to this post, the SQL generated groups by metric '2016-05-24' - "Invoices.csv"."f60485duedate" + 1
, whereas the only group by that we would like is per customer name.
Could you please help up with the solution of the dead-end that we are facing?
Aging report (that we are trying to create) is a very standard one in a variety of businesses and is considered as a must-have by our client.
With regards