Straight Table Not Reading formula Correctly

[ryanfoster84]

Hi,

I have created as summary to display certain values using text boxes (See Below)

I have also created a straight table so that the user can export to excel. To get the text into a table I done an inline load (see below)

Dim:

Load * Inline [

Summary
1. Gross Positive Volume YoY
2. Gross Negative Volume YoY
3. Net YoY Change
4. YoY % Change
5. Options Contribution to Gross Growth
6. Futures Contribution to Gross Growth
7. Options Contribution to Gross Decline
8. Futures Contribution to Gross Decline
9. Gross Futures Positive
10. Gross Futures Negative
11. Net YoY Futures Change
12. Gross Options Positive
13. Gross Options Negative
14. Net YoY Options Change
15. Gross Gain YoY (F+O Netted by Product)
16. Gross Loss YoY (F+O Netted by Product)
17. Net YoY Change
];

So to create the straight table I pulled in the dimension Summary and then created an expression to get the values (see below)

=IF (Summary = '1. Gross Positive Volume YoY',NUM($(vGrossPositiveVolumeYoY),'#,##0'),

IF (Summary = '2. Gross Negative Volume YoY',NUM($(vGrossNegativeVolumeYoY),'#,##0'),

IF (Summary = '3. Net YoY Change',Num($(vNetYoYChange),'#,##0'),

IF (Summary = '4. YoY % Change',Num($(vNetYoYChange) / $(vMinYearVolume),'#,##%'),

IF (Summary = '5. Options Contribution to Gross Growth',(NUM(SUM(AGGR(IF(
IF($(vOptionsYoY)>0, ($(vOptionsYoY))/($(vGrossPositiveVolumeYoY)),($(vOptionsYoY))/(FABS($(vGrossNegativeVolumeYoY)))) >0 ,
IF($(vOptionsYoY)>0, ($(vOptionsYoY))/($(vGrossPositiveVolumeYoY)),($(vOptionsYoY))/(FABS($(vGrossNegativeVolumeYoY)))),0),[Product Line],[Product Description])),'#,##.##%')),

IF (Summary = '6. Futures Contribution to Gross Growth',NUM(SUM(AGGR(IF(
IF($(vFuturesYoY) >0,($(vFuturesYoY))/($(vGrossPositiveVolumeYoY)),($(vFuturesYoY))/(FABS($(vGrossNegativeVolumeYoY)))) >0 ,
IF($(vFuturesYoY) >0,($(vFuturesYoY))/($(vGrossPositiveVolumeYoY)),($(vFuturesYoY))/(FABS($(vGrossNegativeVolumeYoY)))),0),[Product Line],[Product Description])),'#,##.##%'),

IF (Summary = '7. Options Contribution to Gross Decline',NUM(SUM(AGGR(IF(
IF($(vOptionsYoY)>0, ($(vOptionsYoY))/($(vGrossPositiveVolumeYoY)),($(vOptionsYoY))/(FABS($(vGrossNegativeVolumeYoY)))) <0 ,
IF($(vOptionsYoY)>0, ($(vOptionsYoY))/($(vGrossPositiveVolumeYoY)),($(vOptionsYoY))/(FABS($(vGrossNegativeVolumeYoY)))),0),[Product Line],[Product Description])),'#,##.##%'),

IF (Summary = '8. Futures Contribution to Gross Decline',NUM(SUM(AGGR(IF(
IF($(vFuturesYoY) >0,($(vFuturesYoY))/($(vGrossPositiveVolumeYoY)),($(vFuturesYoY))/(FABS($(vGrossNegativeVolumeYoY)))) <0 ,
IF($(vFuturesYoY) >0,($(vFuturesYoY))/($(vGrossPositiveVolumeYoY)),($(vFuturesYoY))/(FABS($(vGrossNegativeVolumeYoY)))),0),[Product Line],[Product Description])),'#,##.##%'),

IF (Summary = '9. Gross Futures Positive',Num($(vGrossFuturesPositive),'#,##0'),

IF (Summary = '10. Gross Futures Negative',NUM($(vGrossFuturesNegative),'#,##0'),

IF (Summary = '11. Net YoY Futures Change',NUM($(vGrossFuturesNegative) + $(vGrossFuturesPositive),'#,##0'),

IF (Summary = '12. Gross Options Positive',NUM($(vGrossOptionsPositive),'#,##0'),

IF (Summary = '13. Gross Options Negative',NUM($(vGrossOptionsNegative),'#,##0'),

IF (Summary = '14. Net YoY Options Change',NUM($(vGrossOptionsPositive) + $(vGrossOptionsNegative),'#,##0'),

IF (Summary = '15. Gross Gain YoY (F+O Netted by Product)',NUM($(vGrossGainYoY),'#,##0'),

IF (Summary = '16. Gross Loss YoY (F+O Netted by Product)',NUM($(vGrossLossYoY),'#,##0'),

IF (Summary = '17. Net YoY Change',NUM($(vGrossGainYoY) +$(vGrossLossYoY),'#,##0'),0
)))))))))))))))))

The issue I am having is that 5,6,7 & 8 are not appearing in the table:

All the THEN parts of the above formulas are used to get the values in the first screen shot.

Any assistance would be great. All the data displayed is test data.

[marcus_sommer]

Are you sure that the expressions by 5-8 from the textboxes are exactly the same like in the straight-table? If yes, take one and put the expression like below into an empty pivot and look within the expression-header how the variables will be interpreted. If this didn't create a hint reduce the expression by commenting various parts and/or replacing them with 1,2,3 or a,b,c to see which parts are working and which not:

(NUM(SUM(AGGR(IF(
IF($(vOptionsYoY)>0, ($(vOptionsYoY))/($(vGrossPositiveVolumeYoY)),($(vOptionsYoY))/(FABS($(vGrossNegativeVolumeYoY)))) >0 ,
IF($(vOptionsYoY)>0, ($(vOptionsYoY))/($(vGrossPositiveVolumeYoY)),($(vOptionsYoY))/(FABS($(vGrossNegativeVolumeYoY)))),0),[Product Line],[Product Description])),'#,##.##%'))

- Marcus

[avinashelite]

check whether you have data for for those data for not ?

[sunny_talwar]

Would you be able to share a sample to look at the issue?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[ryanfoster84]

Hi,

I have just discovered that when you click on 5. Options Contribution to Gross Growth the value displays, very strange...

[marcus_sommer]

Did you try my suggestion?

- Marcus

[ryanfoster84]

yes

I used the formula like above and it returns this, the correct answer but obviously not against the correct field since I have specified just returned the first row from the summary table

[sunny_talwar · Accepted solution]

Try this:

IF (Summary = '5. Options Contribution to Gross Growth',(NUM(SUM(TOTAL AGGR(IF(

IF($(vOptionsYoY)>0, ($(vOptionsYoY))/($(vGrossPositiveVolumeYoY)),($(vOptionsYoY))/(FABS($(vGrossNegativeVolumeYoY)))) >0 ,

IF($(vOptionsYoY)>0, ($(vOptionsYoY))/($(vGrossPositiveVolumeYoY)),($(vOptionsYoY))/(FABS($(vGrossNegativeVolumeYoY)))),0),[Product Line],[Product Description])),'#,##.##%')),

[ryanfoster84]

that worked