Including/Excluding Fields in Calculations

[raynac]

I have built a view where a specific calculation in a chart is, for example,

FieldA + FieldB - FieldC + FieldD

I have been asked to make it so that FieldD can be included or excluded from the calculation by our clients, dependent on how they measure their success.

I had pictured making a selection in a selection box that said "Include FieldD" and "Exclude FieldD", but I cannot see how that would work.  I have built both formulas as two separate fields in the load script (NumWithoutD and NumWithD) but I cannot figure out how to make the charts use one or the other based on some sort of client selection.

If anyone one has suggestions that would enable me to add this functionality, I'd be very appreciative.

Thank you!

[robert99]

Step1

Set up a variable

say vShowColumn (or whatever)

Step2

Use this variable in 2 text boxes (or button)

Action / external / set variable / variable = vShowColumn and   value = 1 (name all

Action / external / set variable / variable = vShowColumn and   value = 2  (named exclude Field D)

Step 3

Set up the various expression columns as required

FieldA + FieldB - FieldC + FieldD

FieldA + FieldB - FieldC

Step4

Then hide the above expression column using this variable

Expression / conditional

vShowColumn = 1

vShowColumn = 2

[#3 · Accepted solution]

Hi,

Use one horizontal List Box, with two values Include and Exclude from one inline table under column vSelection.

In expression of chart use if(getfieldselection(vSelection)='Inlcude',A+B+C,A+B).

Regards,

Navdeep

[MarcoWedel]

Hi,

one possible solution could be:

table1:
LOAD Ceil(Rand()*10) as FieldA,
    Ceil(Rand()*10) as FieldB,
    Ceil(Rand()*10) as FieldC,
    Ceil(Rand()*10) as FieldD
AutoGenerate 30;

tabwoD:
LOAD Dual(Pick(RecNo(),'NumWithoutD','NumWithD'),RecNo()-1) as [w/o D]
AutoGenerate 2;

hope this helps

regards

Marco

[raynac]

Hi RJ,

Thanks so much for your help!  This part of the project was less about columns and more about changing line and bar graphs, but I am keeping this on file for later!

Rayna

[raynac]

Thanks Navdeep!  This worked like a charm.

Best,

Rayna

[raynac]

HI Marco,

Thank you so much for your help.  Unfortunately, my knowledge of Qlikview is limited enough that I could not quite understand how to implement this with my dataset.

I did find a solution, however, and I appreciate your assistance.

Best,
Rayna