topic Re: Always need to aggregate in lowest level in QlikView

Always need to aggregate in lowest level

pavan045 — Sun, 09 Oct 2016 08:12:27 GMT

Hey Everyone,

I have Data something like following table.

Month	Territory	Completed Calls	Targeted Calls
Jan	T1	10	12
Feb	T1	12	10
Mar	T1	14	9
Apr	T1	11	8
Jun	T1	9	12
Jan	T2	8	7
Mar	T3	6	10
Jun	T4	20	25

In a straight table I need to show the # of completed calls exceeding the targeted calls using the dimensions Month, Territory and some other, But Number of calls should always be aggregated in the Territory level.

# of Calls Exceeding Target = Completed Calls - Targeted Calls

if the # of Calls Exceeding Target value is less than zero than 'N\A' should be shown instead of value.

Suppose for the month Jan there are 2 territories associated (T1 and T2). So if want to see the number of completed calls exceeding targeted calls it should be like following calculations.

Jan ------> T1 -----> Completed Calls (10) is less than Targeted calls(12) -------> N/A

T2 ------> Completed Calls(8) is greater than Targeted calls(7) -------> # of Calls Exceeding Target is 1

So in the Straight table for Month Jan I need to show # of Calls Exceeding Target as 1. Here For T1 Completed calls is less than Targeted calls so we should not consider T1 in the aggregation.

so I want show something like the following Straight table

Month	# of Calls Exceeding Target
Jan	1
Feb	2
Mar	6
Apr	3
Jun	N/A

if you observe above table for the Month Jun, # of calls exceeding target is N/A, Because?

Jun --------> T1 ----------> Completed Calls (9) is less than Targeted calls(12) -------> N\A

T2 ----------> Completed Calls(20) is greater than Targeted calls(12) -------> N\A

So for the month Jun it should be 'N\A'

Please Help me on this

Thanks in advance

Re: Always need to aggregate in lowest level

sunny_talwar — Mon, 10 Oct 2016 12:19:34 GMT

May be try this:

=If(Sum(Aggr(If([Completed Calls] - [Targeted Calls] <= 0, 0, [Completed Calls] - [Targeted Calls]), Month, Territory)) = 0, 'N/A', Sum(Aggr(If([Completed Calls] - [Targeted Calls] <= 0, 0, [Completed Calls] - [Targeted Calls]), Month, Territory)))

Re: Always need to aggregate in lowest level

swuehl — Mon, 10 Oct 2016 12:46:15 GMT

You could also use RangeMax() to simplify the inner if() statement and if you could live with zero putting into place instead of 'N/A', you could shorten the expression to

=Sum(Aggr( Rangemax( [Completed Calls] - [Targeted Calls],0), Month, Territory))

Month	# calls exceeding target
	11
Jan	1
Feb	2
Mar	5
Apr	3
Jun	0

(remember to disable 'suppress zero value' on presentation tab).

Re: Always need to aggregate in lowest level

pavan045 — Mon, 10 Oct 2016 15:28:20 GMT

Hi Sunny,

Thank you for you answer

Month is not the only dimension I need to use in the staright table, there might be another 5 or 6 other dimensions. I can't aggregate on all the 6 dimensions.

Please let me know If you got any alternative ideas

Thank you

Re: Always need to aggregate in lowest level

sunny_talwar — Mon, 10 Oct 2016 15:45:42 GMT

Pavan Kalyan wrote:

I can't aggregate on all the 6 dimensions.

Why not?

Re: Always need to aggregate in lowest level

pavan045 — Tue, 11 Oct 2016 06:39:38 GMT

Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

Pavan Kalyan wrote:

I can't aggregate on all the 6 dimensions.

Why not?

I don't want to do this aggregation, because of performance issues and dimensions may change based on the user selections as per requirement.

Please let me know if you have any alternative ideas.

Thank you

Re: Always need to aggregate in lowest level

sunny_talwar — Tue, 11 Oct 2016 16:04:12 GMT

I don't have any ideas, may be swuehl‌ might be able to help. We will wait for his response

Best,

Sunny

Re: Always need to aggregate in lowest level

swuehl — Tue, 11 Oct 2016 16:05:31 GMT

Maybe start with a more detailed description of your requirements and setting?

Also a small sample QlikView application that demonstrates the context we need to develop a solution for might help.

Month	Territory	Completed Calls	Targeted Calls
Jan	T1	10	12
Feb	T1	12	10
Mar	T1	14	9
Apr	T1	11	8
Jun	T1	9	12
Jan	T2	8	7
Mar	T3	6	10
Jun	T4	20	25

Month	Territory	Completed Calls	Targeted Calls
Jan	T1	10	12
Feb	T1	12	10
Mar	T1	14	9
Apr	T1	11	8
Jun	T1	9	12
Jan	T2	8	7
Mar	T3	6	10
Jun	T4	20	25

Month	Territory	Completed Calls	Targeted Calls
Jan	T1	10	12
Feb	T1	12	10
Mar	T1	14	9
Apr	T1	11	8
Jun	T1	9	12
Jan	T2	8	7
Mar	T3	6	10
Jun	T4	20	25