topic Re: How can I use data but not display it. in QlikView

How can I use data but not display it.

stig1984 — Wed, 25 Nov 2020 16:16:04 GMT

I have a set of data (below) in which I need to use the full set of data but I do now want to display all of it.

In the below data I need to use class 1, class 2 & waste to calculate the percentage produced, however I only want to display class 2 & waste in tables and charts (i.e.; use class 1 but hide it from view)

How would I go about this? Dimension limits only seem to refer to numerical based selections (top 10 etc.)

Re: How can I use data but not display it.

sunny_talwar — Mon, 21 Mar 2016 14:27:11 GMT

Not entirely sure what you are trying to do, do you have a sample you can share?

Re: How can I use data but not display it.

stig1984 — Mon, 21 Mar 2016 14:39:02 GMT

Basically what I after is the below chart but not showing class 1. The class 1 is used in the calculation for the percentage so needs to be considered but I don't want to show it on the charts. (overwhelms the key data of class 2 and waste).

Re: How can I use data but not display it.

t_chetirbok — Mon, 21 Mar 2016 14:50:01 GMT

Hi!

Can you post your data file?

Re: How can I use data but not display it.

stig1984 — Mon, 21 Mar 2016 15:52:12 GMT

Data file attached.

Re: How can I use data but not display it.

sunny_talwar — Mon, 21 Mar 2016 17:30:29 GMT

May be this?

=sum({<Prod_Class -= {'CLASS 1'}>}RMInnerCount)/sum( {1} TOTAL <ProductionRun> RMInnerCount)

Re: How can I use data but not display it.

stig1984 — Tue, 22 Mar 2016 10:14:41 GMT

That works well if I have the ProductionRun present in the table.

If I create an alternative table which does not include the ProductionRun (i.e.; dimension is purely Category / Product) then I am getting incorrect results.

Might I be better doing the calculation in the script and creating a variable?

Re: How can I use data but not display it.

sunny_talwar — Tue, 22 Mar 2016 12:16:48 GMT

If you remove ProductionRun, you loose the ability to look the uniqueness provided by ProductionRun and Category by itself will start grouping up. So essentially if you just have Category, you will only see one of PEPPER, TOPFRUIT, STONEFRUIT...and so on...

Would you then would like to average or Sum or Min or Max the values among differen ProductionRun for a single Category and Prod_Class?

Re: How can I use data but not display it.

swuehl — Tue, 22 Mar 2016 12:22:04 GMT

What you want to do (use dimensional data without displaying it in the table) can be achieved using advanced aggregation:

AGGR...

Pitfalls of the Aggr function

Re: How can I use data but not display it.

stig1984 — Tue, 22 Mar 2016 12:48:16 GMT

I'd like the sum of the percenatges of product generated per run.

For example (from Excel)

What I am getting is (percentages in top table correct, percentages in lower table long way form correct - cucumber class 2 should be 3.24%):

Re: How can I use data but not display it.

sunny_talwar — Tue, 22 Mar 2016 12:59:05 GMT

Try this expression:

=Sum(Aggr(Sum({<Prod_Class -= {'CLASS 1'}>}RMInnerCount), ProductionRun, Product, Prod_Class))/Sum({1}Aggr(Sum({1}TOTAL <ProductionRun> RMInnerCount), ProductionRun, Product, Prod_Class))

Dimension

Product

Prod_Class

Re: How can I use data but not display it.

stig1984 — Tue, 22 Mar 2016 15:12:35 GMT

This isn't quite working to give the right result.

I have split the formula into two parts in the table to see why - the first part (Sum(Aggr(Sum({<Prod_Class -= {'CLASS 1'}>}RMInnerCount), ProductionRun, Product, Prod_Class)) - inner count in table) is OK - however the 2nd part (Sum({1}Aggr(Sum({1}TOTAL <ProductionRun> RMInnerCount), ProductionRun, Product, Prod_Class)) - total inner count in table) is returning the wrong figures still -should be 163,139 for cucumber.

I'm guessing the issue is with the identifiers so I'll play around a bit with these.

Re: How can I use data but not display it.

sunny_talwar — Tue, 22 Mar 2016 16:21:54 GMT

What about this:

=Sum(Aggr(Sum({<Prod_Class -= {'CLASS 1'}>}RMInnerCount), ProductionRun, Product, Prod_Class))/Sum({1}TOTAL <Product> Aggr(Sum({1}TOTAL <ProductionRun> RMInnerCount), ProductionRun, Product, Prod_Class))

Re: How can I use data but not display it.

swuehl — Tue, 22 Mar 2016 16:38:16 GMT

Or maybe remove the TOTAL qualifier from the inner aggregation:

=Sum({1}TOTAL <Product> Aggr(Sum({1} RMInnerCount), ProductionRun, Product, Prod_Class))

Re: How can I use data but not display it.

stig1984 — Tue, 22 Mar 2016 17:07:15 GMT

Both of these answers are now giving me the same result for each class, but it's still not the right result.

Correct answer for Total inner count (to divide by) is 227,008 for data selected.

Swuehl's answer is giving me 298,826

Sunny T's answer is giving me 429,906.

I have stripped out a load of data and will attach the report for assessing.

Re: How can I use data but not display it.

stig1984 — Tue, 22 Mar 2016 17:08:52 GMT

File attached

Re: How can I use data but not display it.

swuehl — Tue, 22 Mar 2016 20:35:54 GMT

Maybe just remove the set analysis:

=Sum(TOTAL<Product> Aggr(Sum(FPInnerCount),ProductionRun,Product,Prod_Class))

Re: How can I use data but not display it.

stig1984 — Wed, 23 Mar 2016 10:34:12 GMT

Still not returning correct data unless filtered by an early run. It seems to use earlier runs as part of the data (i.e.; not restricting to production run but using earlier runs as part of the data)

I kept playing around with your formula above and the below works

Sum(ALL<Product>Aggr(Sum({<Prod_Class>}FPInnerCount),ProductionRun,Product,Prod_Class))

Not sure why this works but I just kept throwing things at it until something stuck.

Thanks to both of you for your help - it gave me stepping stones along the way and I would never have got anywhere without them.

Re: How can I use data but not display it.

sunny_talwar — Wed, 23 Mar 2016 12:52:09 GMT

I am not sure what you are seeing, but I am seeing the same results based on Stefan's expression (with addition of the inner set analysis)

=Sum(TOTAL<Product> Aggr(Sum({<Prod_Class>}FPInnerCount),ProductionRun,Product,Prod_Class))

vs. yours

Sum(ALL<Product>Aggr(Sum({<Prod_Class>}FPInnerCount),ProductionRun,Product,Prod_Class))

Tried clearing out some selections and did not find any differences in the number. What selections are making the two expressions deviate for you?

Re: How can I use data but not display it.

swuehl — Wed, 23 Mar 2016 13:00:10 GMT

The ALL qualifier is deprecated and should not be used anymore.

Not sure if it has supported a field list ('ALL<Product>'), AFAIK, ALL is a synonym for {1} TOTAL