topic Re: Including/Excluding Fields in Calculations in QlikView

Including/Excluding Fields in Calculations

raynac — Thu, 18 Dec 2014 18:13:07 GMT

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!

Re: Including/Excluding Fields in Calculations

robert99 — Thu, 18 Dec 2014 18:23:03 GMT

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

Re: Including/Excluding Fields in Calculations

Thu, 18 Dec 2014 18:41:32 GMT

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

Re: Including/Excluding Fields in Calculations

MarcoWedel — Fri, 19 Dec 2014 00:10:23 GMT

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

Re: Including/Excluding Fields in Calculations

raynac — Tue, 23 Dec 2014 19:36:40 GMT

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

Re: Including/Excluding Fields in Calculations

raynac — Tue, 23 Dec 2014 19:37:10 GMT

Thanks Navdeep! This worked like a charm.

Best,

Rayna

Re: Including/Excluding Fields in Calculations

raynac — Tue, 23 Dec 2014 19:44:47 GMT

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