Oracle Communities

Oracle Analytics and AI

Child Item

If create calculate column it will automatically affected another measure column in report

User_HQ9TE

If apply some formula in column it will automatically affected another measure column in report

IMG_20240305_213548.jpg

Without create calculate column for leads column total is 4532 and apply calculate column leads column total is 16104

How to get leads total 4532 when apply calculate column.

Could you please help me anyone.

IMG_20240305_213649.jpg

IMG_20240305_213701.jpg

The # of leads values based on activity type column so I want to leads based on channel details, campaign code, campaign name

IMG_20240306_133617.jpg

When I checked the query it's effected the leads column values but we need to add market code wise condition

If anyone please help me.

IMG_20240306_194015.jpg

IMG_20240306_193752.jpg

Thanks

Find more posts tagged with

OBIEE

Reporting

Oracle Fusion Analytics

Accepted answers

All comments

Gianni Ceresa

Oracle Analytics doesn't generate numbers randomly, every single number has a reason.

How does that number get to your screen? You analysis generate a LSQL query (the columns you selected, the formulas you enter), this is translated into a query the physical source can understand based on your semantic model (rpd).

If you want to understand where that number comes from, why it is there, look at the query generated by your analysis. Look at the physical query and that will tell you what's going on.

If you write aggregation expression in your formula in the analysis, it generally means that your RPD has issues because the measures should be available there directly.

But right now only you can find out what's going on and why: look at the logs of your query, what has been generated and fix it based on that.

Chere-Oracle

The "Activity Type" column comes from the "- Activity" presentation folder. When you added this calculated column, was it the first time any column from "- Activity" folder was added?

Here is my guess: "- Activity" has more than 1 row per campaign. When you add the folder to the analysis, you get "# of Leads" multiplied by number of rows in "- Activity" folder.

The fix will depend on the aggregation method that is used for "# of Leads" column. You can possibly divide "# of Leads" by COUNT("- Activity"."Activity Type").

Renuka Nuguru-Oracle

Can you provide the logical sql

User_HQ9TE

https://community.oracle.com/products/oracleanalytics/discussion/comment/52504#Comment_52504

IMG_20240306_194015.jpg

IMG_20240306_193752.jpg

When I checked the query it's effected the leads column values but we need to add market code wise condition

Renuka Nuguru-Oracle

I believe this is Analysis report, can you get the logical sql for the report where you are seeing incorrect results.

Click on the Advanced tab on the Analysis report and copy and paste the logical sql, not the database sql.

Dori S.-Oracle

As a tip for the future, it would be easier for us to review your code if it were in a text, not a photograph of a computer screen.

I can only guess based on reviewing line by line (img-20240306-194015.jpg) you are trying to get the value 15 in column c1, which you were able to do by commenting out the tables W_XACT_TYPE_D and W_ACTIVITY_F in the joins and corresponding filters.

I agree with Chere-Oracle that it seems like you have more records in those 2 tables, than expected for these conditions.

The best way to address this is to completely simplify your SQL and hand-write some basic SQL (starting with 2 tables at a time), doing some basic data analysis. Once you determine where those extra records are, then you can determine what your SQL should look like to get the desired results. Perhaps you need a distinct count or some other function or additional filters, but without knowing your requirement, it is hard to say.

This one might benefit from opening an SR so someone can help you look closer at the data relationships and how to address your needs in the model and/or analysis logic.

User_HQ9TE

https://community.oracle.com/products/oracleanalytics/discussion/comment/52504#Comment_52504

This is the Analysis report logical query

image001 (1).png

User_HQ9TE

https://community.oracle.com/products/oracleanalytics/discussion/comment/52534#Comment_52534

Could you please help me

User_HQ9TE

https://community.oracle.com/products/oracleanalytics/discussion/comment/52563#Comment_52563

Hi renuka,

Please help me.any suggestion

GayathriAnand-Oracle

As Dori mentioned we may have to look to analyze this analysis and sql in more detail. So suggest you to create SR with Oracle Support so we can review and offer further assistance.