Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ryanfoster84
Creator
Creator

Straight Table Not Reading formula Correctly

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.

1 Solution

Accepted Solutions
sunny_talwar

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])),'#,##.##%')),

View solution in original post

8 Replies
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
Creator
Creator
Author

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
Creator
Creator
Author

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

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
Creator
Creator
Author

that worked