To GoodData homepage
Solved

Table visual won't convert to chart

  • 11 April 2023
  • 6 replies
  • 73 views
G
  • GSawdy
  • Participating Frequently
  • 9 replies

Hello

 

I have created a conditional metric to calculate revenue (see screenshot below).

 

I want to show the revenue by date in a bar chart and have an attribute campaign filter so I can select the revenue for the specific attribute. I have created a table with the date, the attribute and the metric and it is showing me the revenue by campaign and date. However, when I try to change this to a bar chart, removing the campaign attribute and have only revenue and date, the visual breaks.The campaign attribute and pricing model is based off a different attribute table in my model to the fact table which includes the date.

I need a chart to show overall revenue by date, that can be filtered by the campaign.

 

Thanks

 

 

icon

Best answer by Moises Morales 18 April 2023, 09:43

View original

6 replies

Francisco Antunes
Rank

Hello,

Thanks for putting this in. I see that you’ve built an insight that works as a table, when the main metric is sliced by both Campaign and Date; However, when you switch it to a Bar chart and remove the Campaign attribute, the insight breaks.

As the CASE article explains, using CASE without an Aggregation container makes it unable to be sliced by attributes from other dimensionalities. I believe that is the case here, since the Date attribute is from a different dimension as the rest of the metric. This also explains why the insight works when also sliced by Campaign (it is in the same dimension as the components of the metric).

The solution would be to encase the entire metric inside a SUM aggregation, to enable it to be sliced by attributes from other dimensionalities. The article I shared above explains how to do this in more detail, in the How to Use CASE Correctly section.

Please try implementing the solution above and let us know if it works! 

 

Best,

Francisco

G

Hi Francesco the date attribute is from the same dimension as the sum of facts. The pricing model and net cpm metric is from a different dimension if that makes any difference?

 

I have encased the metric in a SUM aggregation, however it has not solved the issue.

 

 

Francisco Antunes
Rank

Hello,

Thank you for the additional information. I see that placing the metric inside a SUM aggregation did not resolve the issue.

I would like to suggest a different approach for trying to connect this metric to the desired dimensionality. It is called Explicit Lifting. The article is from our Legacy documentation, and as such some of the screenshots will be out-of-date for your case, but the procedure itself is still valid. The idea is that we can use the BY keyword to connect the metric to a dimensionality that it wouldn’t be able to use otherwise. The exact implementation will depend on your LCM, but I would suggest trying out adding BY Campaign , for example, at the end of the metric. Make sure to keep the SUM aggregation. 

 

If the solution above also does not work, would you mind sending us a couple of additional screenshots?

  • Please take a screenshot of how the insight looks on the Analytical Designer when you switch to the bar chart (we’d like to see how the visual ‘breaks’)
  • Please also take a screenshot of the parts of your LDM relevant to this metric.
  • Would also be nice to know from which datasets the objects being used in the metric are from as well.

Make sure to redact any important information from the images. The goal is for us to best understand how the metric is situated in your LDM, so we can get it to work correctly.

I look forward to hearing back from you!

Best regards,

Francisco

 

G

Hi I tried adding ‘By campaign’ but this did not work. 

I have attached a screenshot of the error.

Also attached is a screenshot of LDM, ‘Test advertiser summary’ and ‘campaign media data_combiend’ and the date table are the relevant tables. The summary and media data are joined by the campaign column. Note I added attribute columns to the campaign media data table in an attempt to get the desired metric to work. However my pricing model and net cpm metric are based off of attributes from the test advertiser summary table.

These tables are views from our redshift database, which are based off queries joining several of our relational data tables together.

 

Francisco Antunes
Rank

Hi,

Thank you for the screenshots and additional context. In this case, the best path forward would be for us to check your workspace directly and troubleshoot the insight from there.

We will contact you via email directly very soon with the next steps!

 

Best,

Francisco

Moises Morales
Rank
Userlevel 3

Hi, 

For future reference purposes, let me send over here as well our findings: 

I'm afraid it won't be possible to calculate the cases using only a date dimension since this is considered incorrect syntax. More specifically the conditional operations can only be triggered by the values of the related attribute, e.g. sum (impressions)/1000 * Net CPM and not by a date attribute, hence it is necessary to add "Campaign" to the insight, otherwise, it won't calculate.

The following metric does not make much sense in the context of your specific requirements, but for the purpose of demonstrating the above, notice how it is possible to break down the metric by using only a date attribute in your insight if we use constants with the case statement:

select case when Pricing Model = CPM then 3, when Pricing Model = CPA then 1, when Pricing Model = 0 then 2 else 0 END BY Campaign

Having said that, you will always need to include an attribute other than a date if any calculation needs to be computed to evaluate the conditional.

 

-Moises

Reply


Join us on
Welcome to the GoodData Community!Cookie settings