Multi-level calculation using set analysis/variables

[jessica_webb]

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.

[swuehl]

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...

[swuehl · Accepted solution]

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.

[jessica_webb]

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

[swuehl]

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

[jessica_webb]

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

[swuehl]

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)

or

=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)

or

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

[jessica_webb]

Thanks again Stefan. Will give all this a go!

[jessica_webb]

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!

[swuehl]

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