Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
pavan045
New Contributor III

Always need to aggregate in lowest level

Hey Everyone,

I have Data something like following table.

MonthTerritoryCompleted CallsTargeted Calls
JanT11012
FebT11210
MarT1149
AprT1118
JunT1912
JanT287
MarT3610
JunT420

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

Feb2
Mar6
Apr3
JunN/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

7 Replies
Highlighted

Re: Always need to aggregate in lowest level

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

Capture.PNG

Highlighted
MVP
MVP

Re: Always need to aggregate in lowest level

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
Jan1
Feb2
Mar5
Apr3
Jun0

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

Highlighted
pavan045
New Contributor III

Re: Always need to aggregate in lowest level

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

Highlighted

Re: Always need to aggregate in lowest level

Pavan Kalyan wrote:

I can't aggregate on all the 6 dimensions.

Why not?

Highlighted
pavan045
New Contributor III

Re: Always need to aggregate in lowest level

Sunny T &lt;span class=&quot;icon-status-icon icon-mvp&quot; title=&quot;Mvp&quot;&gt;&lt;/span&gt; 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

Highlighted

Re: Always need to aggregate in lowest level

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

Best,

Sunny

Highlighted
MVP
MVP

Re: Always need to aggregate in lowest level

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.