topic Multi-level calculation using set analysis/variables in QlikView

Multi-level calculation using set analysis/variables

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

Hi,

As you may have been able to tell from the title, I'm not quite sure how to go about this, so I'm hoping that someone with a bit of expertise could help!

I basically want to end up with a straight table, with six separate calculations, which build on each other.

The first six columns of my example table are the data I'm loading in, the last six (in purple) are the results I'm hoping to achieve in QV.

ID	Type	Size	Name	Score	Old	New	R1	R2	R3	R4	R5	R6
1877	A	10	1	40.67	29	30	0.08	0.08	-0.51	-2.53	-1.25	-1.25
3206	A	10	1	40.00	28	20	-0.82	-0.82	-0.51	-2.53	-1.25	-1.25
7633	A	10	1	40.67	29	20	-0.92	-0.92	-0.51	-2.53	-1.25	-1.25
9454	A	10	1	43.33	33	40	0.71	0.28	-0.51	-2.53	-1.25	-1.25
9816	A	10	1	37.60	26	10	-1.58	-1.58	-0.51	-2.53	-1.25	-1.25
3917	A	10	2	42.67	30	20	-0.99	-0.64	0.01	0.04	-1.25	-1.25
6319	A	10	2	39.33	23	30	0.70	0.35	0.01	0.04	-1.25	-1.25
8833	A	10	2	44.67	34	30	-0.40	-0.40	0.01	0.04	-1.25	-1.25
9454	A	10	2	43.33	31	30	-0.14	0.28	0.01	0.04	-1.25	-1.25
9828	A	10	2	48.67	41	50	0.86	0.86	0.01	0.04	-1.25	-1.25
2670	B	5	1	43.33	14	10	-0.42	-0.42	-0.48	-1.45	-1.26	-1.25
3917	B	5	1	42.67	13	10	-0.30	-0.64	-0.48	-1.45	-1.26	-1.25
6319	B	5	1	39.33	10	10	-0.01	0.35	-0.48	-1.45	-1.26	-1.25
4436	B	5	2	41.33	8	0	-0.75	-0.75	-0.49	-0.98	-1.26	-1.25
6219	B	5	2	40.67	7	10	0.26	0.26	-0.49	-0.98	-1.26	-1.25

R1: =(New - Old)/Size

R2 = Average of R1 for each distinct ID

R3 = Sum of R1 for each distinct Name / Count of all (non distinct) IDs in that distinct Name

R4 = R3 for each distinct Name x Count of all (non distinct) IDs in that distinct Name

R5 = Sum of R4 for each distinct Type / Count of all (non distinct) IDs in that distinct Type

R6 = Sum of R4 / Count of all (non distinct) IDs

Unsurprisingly, I've managed to workout how to calculate R1 myself (New-Old)/Size

But that's it...

The list of IDs, Types, and Names is endless, so I don't want to have to refer to (e.g.) 'Type=A' anywhere, if I can avoid it.

I toyed with the idea of creating a variable from my R1 expression, but then realised that I'm a bit out of my depth, so thought I'd be best off seeing if anyone had any better suggestions first!

Any suggestions would be greatly appreciated - please let me know if you need any additional information.

Re: Multi-level calculation using set analysis/variables

swuehl — Wed, 23 Mar 2016 17:08:12 GMT

Whenever you need to aggregate your data, you would need to create another LOAD with a GROUP BY clause and join this table back to your INPUT table.

Assuming you've created R1 already in your INPUT table LOAD:

LEFT JOIN (INPUT)

LOAD ID,

Avg(R1) as R2

RESIDENT INPUT

GROUP BY ID;

and so on...

Re: Multi-level calculation using set analysis/variables

swuehl — Wed, 23 Mar 2016 17:14:33 GMT

Ah, sorry, missed that you want a front end solution.

R2 could look like

=Avg(TOTAL<ID> (New - Old)/Size )

so you could make use of aggregation functions with the TOTAL qualifier and field list to create your aggregations considerung only part of the dimensions.

Re: Multi-level calculation using set analysis/variables

jessica_webb — Thu, 24 Mar 2016 09:41:39 GMT

Hi Stefan,

Thank you so much for your help. The TOTAl qualifier is exactly the thing I needed, and I'm slowly but surely creating the expressions I need!

The only thing is, some of the expressions are getting a bit lengthy. I'm assuming it would be more efficient if I create variables and then I can refer to them rather that repeating the expression for R1?

Anyway, will have a play around, but thanks again.

Jess

Re: Multi-level calculation using set analysis/variables

swuehl — Thu, 24 Mar 2016 10:09:45 GMT

Hi Jessica,

glad that it worked for you.

Yes, variables could be helpful here. Just consider that it makes a different how you define and call your variables, i.e. if you want a text or value replacement / evaluation. See for more details

The Magic of Variables

The Little Equals Sign

The Magic of Dollar Expansions

Regards,

Stefan

Re: Multi-level calculation using set analysis/variables

jessica_webb — Thu, 24 Mar 2016 10:33:24 GMT

Great advice - thanks!

Having read through those articles, I think I'll just stick to using straight expressions after all! Worried I'll over-complicate things (and make mistakes) with variables.

I'm wondering if there's something else you could advise me on. I want my expressions to ignore all selections. I know I can do this with set analysis, but is there a workaround if not using SA?

To take my first expression as an example, (New-Old)/Size, where would I put the '1' to ignore selections?

Jess

Re: Multi-level calculation using set analysis/variables

swuehl — Thu, 24 Mar 2016 10:41:33 GMT

Remember that we need to use aggregation functions in expressions:

Use Aggregation Functions!

If we leave them out, QlikView will implicitely apply Only() aggregation function, which might be handy, if you know what's happening.

Your expression could therefore also be written as:

=(Only(New)-Only(Old) ) / Only(Size)

=Only( (New-Old) / Size)

You can apply set identifier {1} to ignore all selections (or other set expressions ignoring only some field selections) to each aggregation function, like

=( Only({1}New)-Only({1}Old) ) / Only({1}Size)

=Only({1} (New-Old) / Size)

Re: Multi-level calculation using set analysis/variables

jessica_webb — Thu, 24 Mar 2016 11:00:49 GMT

Thanks again Stefan. Will give all this a go!

Re: Multi-level calculation using set analysis/variables

jessica_webb — Thu, 24 Mar 2016 11:55:50 GMT

Hi Stefan,

Sorry to keep asking so many questions, but still not managing to get this to work, either as variables or as expressions.

The TOTAL qualifier seems to be causing me issues later down the line - I guess as it's evaluating all TOTAL qualifiers (when I'm reusing expressions).

R1: Avg(TOTAL<Name, ID> New-Old)/Size

This works fine - I used Avg in the end instead of Only after reading the article on aggregation.

I want the result to be for each ID, for each Name.

R2: Avg(TOTAL<ID>(Avg(TOTAL<Name, ID> New-Old)/Size))

This does not work. If I save R1 as a variable, I just get missing values for R2.I guess because it doesn't like TOTAL being used twice.

I basically want each new R expression to use the value of the previous R expression, without reevaluating the whole thing!

Re: Multi-level calculation using set analysis/variables

swuehl — Thu, 24 Mar 2016 12:09:37 GMT

No, that's because you are embedding aggregations ( which returns a scalar) into another aggregation (which expects something dimensional as input), so it's in general not possible to do that without using advanced aggregation:

AGGR...‌

Pitfalls of the Aggr function

So it might look like

=Avg(TOTAL<ID> Aggr( (Avg(TOTAL<Name, ID> New-Old) /Size ), Name, ID))

You probably need to adapt the aggr() dimensions to get the correct results.

As you see, it's getting more and more complex, due to your multi-level calculations. If you can put some calculations that are not depending on user selections into the script part, I would recommend to do so.

It looks like that at the end of this day, you've managed to learn all advanced QlikView features

Re: Multi-level calculation using set analysis/variables

jessica_webb — Thu, 24 Mar 2016 12:16:07 GMT

Thank you again!

It definitely is getting pretty complex - slightly out of my depth I think!

Actually, none of it depends on user selections. I had thought about doing it in the script, but I have even less experience of doing that.

I did try before with

LOAD

R1 as (New-Old) / Size);

But gave up pretty quickly when that didn't work.

I'll take a look at those articles, try and load the calculations in the script, and fingers crossed won't bother you again!

Many thanks,

Jess

Re: Multi-level calculation using set analysis/variables

swuehl — Thu, 24 Mar 2016 12:22:51 GMT

You would not be able to do the same aggregations with TOTAL in the script, you would need to create temporary tables with the proper granularity and aggregation functions and then join these tables back to your original table, somehow like I described in my first response.

If you want to create new fields, the syntax is like

LOAD

calculation AS NewFieldName,

e.g.

LOAD

(New-Old) / Size) AS R1,

Re: Multi-level calculation using set analysis/variables

jessica_webb — Thu, 24 Mar 2016 13:42:42 GMT

Now working perfectly.

Looks like your first response was actually the one that was spot on for me!

Thanks once again for all your help with this,

Jess