Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a straight table as follows
- variables
vRule1 = aggr1 (complex aggregation)
vRule2 = aggr2 (complex aggregation)
...
vRuleN = aggrN
- Dimensions - rule, Plan_ID, PERIOD_YEAR, ZIP_CODE, ZIP_LEVELS
- Expressions
Value = calculated based on rule.
Status = status calculated based on Value - RED, BLACK and GREEN.
No problems so far.
I need to hide all the rows that result in BLACK for Status. Since the rows are hidden only if all the expressions are null or 0, and Value and Status depend on each other, I am stuck.
I cannot move the calculation of Value or Status to the load script
The file attached is a mock up. Please note that the actual report has tables created from complex sql queries.
I believe you will have to get both expressions set to null in order to suppress the row.
I applied the < 1 and > 10 conditions to the Value expression to determine whether or not to calculate a value or return null().
I replaced 'NORMAL' in the Status expression with null().
See the attached application.
This works with your mock up. It may get too complicated in your actual application.
Hope this helps.
John.
John,
Thanks for the response. You are right that this won't work for my app. The reason is that Value is calculated within an if statement and the status expression is too. i range to check for high, low and normal varies by rule and TYPE dimensions.
I was wondering if there was some we could refer to these calculations in another straight table?
Regards
Jairam
Hi Jairam.
I not sure if it is possible to reference calculations from another object.
Could you post an application with the actual logic?
John.
John,
I have attached the sample report with 2 tables one for calculating the data and the other for filtering out the ones that are not required.
Jairam
Hi Jairam.
I don't know if or how to bring an expression value from one chart to another. I think we may have to solve this on the one chart.
Would the Report chart display all values for Value and Status from the Calculated Data chart that are in green or red? The Report chart includes the field TYPE. Does this mean you expect 2 rows for every Value/Status combination (FFS and STD)?
Could you attach a spreadsheet showing what you expect the Report chart to look like based on a couple of selections.
Thanks,
John.
John,
I made a mistake.. The two chart have identical dimensions. So the number of rows should be the same for each status except that none of the black rows should be visible.
rule PLAN_ID PERIOD_YEAR ZIP CODE ZIP LEVEL Value Status
1 1 2008 100 Z1 xxxxxx NORMAL
2 1 2008 100 Z1 yyyyy HIGH
3 1 2008 100 Z1 zzzzzz LOW
2 1 2009 100 Z1 xxx NORMAL
4 1 2009 100 Z1 yy HIGH
3 1 2009 100 Z1 zzz LOW
should result in the same number of rows minus the NORMAL normal ones
1 1 2008 100 Z1 xxxxxx NORMAL
2 1 2008 100 Z1 yyyyy HIGH
3 1 2008 100 Z1 zzzzzz LOW
2 1 2009 100 Z1 xxx NORMAL
4 1 2009 100 Z1 yy HIGH
3 1 2009 100 Z1 zzz LOW
i.e the 6 rows are filtered down to only 4.
Hi Jairam.
Unfortunaltely, the only solution I can think of is the one in the application I attached above (May 2 3:35).
I'm not sure why this solution won't work. Maybe you can explain in a bit more detail your statement that it won't work because the values are calculated within if statemtents.
John.