Discussion Board for collaboration related to QlikView App Development.
I have Data something like following table.
|Month||Territory||Completed Calls||Targeted Calls|
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|
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
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)))
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|
(remember to disable 'suppress zero value' on presentation tab).
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
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.
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.
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.