topic Straight Table Not Reading formula Correctly in QlikView

Straight Table Not Reading formula Correctly

ryanfoster84 — Wed, 08 Mar 2017 10:39:04 GMT

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.

Re: Straight Table Not Reading formula Correctly

marcus_sommer — Wed, 08 Mar 2017 11:48:27 GMT

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

Re: Straight Table Not Reading formula Correctly

avinashelite — Wed, 08 Mar 2017 12:03:33 GMT

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

Re: Straight Table Not Reading formula Correctly

sunny_talwar — Wed, 08 Mar 2017 12:13:15 GMT

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

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Straight Table Not Reading formula Correctly

ryanfoster84 — Wed, 08 Mar 2017 13:41:13 GMT

Hi,

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

Re: Straight Table Not Reading formula Correctly

marcus_sommer — Wed, 08 Mar 2017 14:13:12 GMT

Did you try my suggestion?

- Marcus

Re: Straight Table Not Reading formula Correctly

ryanfoster84 — Wed, 08 Mar 2017 14:19:52 GMT

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

Re: Straight Table Not Reading formula Correctly

sunny_talwar — Wed, 08 Mar 2017 14:21:49 GMT

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

Re: Straight Table Not Reading formula Correctly

ryanfoster84 — Wed, 08 Mar 2017 14:24:14 GMT

that worked