topic Re: Aggregation to different levels in QlikView

Aggregation to different levels

Wed, 19 Nov 2014 14:24:47 GMT

I a sheet in my QV that has several dimensions and three measures. I want to be able to aggregate two of the measures to a set of dimensions and the one measure to another set. So for example I have dimensions

and measures 1,2 3.

I want measure 1 to work across all dimensions and measures 2 and 3 to work across only A, B, C. So if lets say the user selects something from dimension F measures 2 and 3 should not aggregate to to that level.

Re: Aggregation to different levels

morganaaron — Wed, 19 Nov 2014 14:30:47 GMT

If you use the total qualifier but exclude the dimensions you want it to calculate over, it should work, so:

=Sum(TOTAL <'A', 'B', 'C'> 2) to sum measure 2 for example.

Re: Aggregation to different levels

Wed, 19 Nov 2014 14:52:21 GMT

so I have the following as a variable currenty SUM({<[P&L Classification]={Income}, RecordType={SLR}>}[Actual]). So based on your example do i need to extend this so that the dims A, B and C are included?

Re: Aggregation to different levels

morganaaron — Wed, 19 Nov 2014 15:12:02 GMT

Yes - if you're already using set analysis, there's an easier way to do it, if you include:

Sum({<[P&L Classification]={'Income'}, RecordType={'SLR'}, F=>}Actual)

That last "F=" will mean it ignores any selections made in the dimension F. You can keep adding those so "F=, E=, D=' and it will ignore any selections over those dimensions.

SUM({<[P&L Classification]={Income}, RecordType={SLR}>}[Actual]).

Re: Aggregation to different levels

Wed, 19 Nov 2014 18:03:59 GMT

so in your example do you mean like this

Sum({<[P&L Classification]={'Income'}, RecordType={'SLR'}, F=>, E=>, D=>}Actual}

Re: Aggregation to different levels

Wed, 19 Nov 2014 18:13:13 GMT

also when i have F etc should this be the name of the attribute?

Re: Aggregation to different levels

morganaaron — Thu, 20 Nov 2014 08:09:06 GMT

That's correct, assuming F, E and D are the names of dimensions that you don't want the figures to be affected by.

Re: Aggregation to different levels

ashwanin — Thu, 20 Nov 2014 08:19:17 GMT

If you want to ignore the Dimensions F,D & E then its better not to include them in Set analysis.

This final expression should be like this :

Sum({<[P&L Classification]={'Income'}, RecordType={'SLR'}>}Actual)

Re: Aggregation to different levels

morganaaron — Thu, 20 Nov 2014 08:41:00 GMT

I think he said he doesn't want a selection in a Dimension F for example to affect the figure, which it would do unless it's put in a total statement or removed from the set in a set analysis statement.

Re: Aggregation to different levels

jagan — Thu, 20 Nov 2014 08:48:21 GMT

Hi,

Try like this

Sum({<[P&L Classification]={'Income'}, RecordType={'SLR'}, F=, E=, D=>} TOTAL <A, B, C>Actual}

In the above expression, D, E F selections are not consider and A,B,C dimensions are not considered.

Hope this helps you.

Regards,

jagan.

Re: Aggregation to different levels

Thu, 20 Nov 2014 15:17:18 GMT

sorry you say in this example ' D, E F selections are not consider and A,B,C dimensions are not considered.'. Which ones will be considered?

Re: Aggregation to different levels

Thu, 20 Nov 2014 15:39:27 GMT

thats worked.

Can i apply the same principle to this: count( distinct JoinKey)

Re: Aggregation to different levels

morganaaron — Thu, 20 Nov 2014 16:13:01 GMT

Yes - most functional expressions (max, min, count, sum etc.) you can apply set analysis to, and so within that you can exclude fields from the set like shown - same goes with the total qualifier!

Re: Aggregation to different levels

Thu, 20 Nov 2014 16:21:49 GMT

okay I tried it but i think I may have my syntax wrong. COuld you give me an example with my count( distinct JoinKey) please

Re: Re: Aggregation to different levels

jagan — Fri, 21 Nov 2014 02:11:08 GMT

Hi,

Can you attach some sample data and your required output? It would be easier and understand and give solution.

Regards,

jagan.

Re: Re: Aggregation to different levels

Fri, 21 Nov 2014 08:34:53 GMT

so pretty much want to do the same as the other example (which works) but now on another expression which is currently set at: count( distinct JoinKey). So in this example, I would want it to not apply to dimensions E, F but apply A, B, C, D.

Re: Aggregation to different levels

Wed, 26 Nov 2014 10:39:32 GMT

can i post the example QVW if you could take a look at?

Re: Aggregation to different levels

morganaaron — Wed, 26 Nov 2014 10:42:53 GMT

Go for it.

Re: Aggregation to different levels

Fri, 28 Nov 2014 13:34:45 GMT

this is the expression I have but it is showing as incorrect syntax.

count({distinct JoinKey}),{[Expense Code Description]=, CostCentreDescription2=, CostCentreDescription3=, CostAnalysisDescription=, CostClassificationDescription=>

Re: Aggregation to different levels

ashwanin — Mon, 01 Dec 2014 05:16:13 GMT

Try below

count({< [Expense Code Description]=, CostCentreDescription2=, CostCentreDescription3=, CostAnalysisDescription=, CostClassificationDescription= >} Distinct JoinKey)