Count of Distinct States based on calls from zones aggregated to State

[#1]

Hello all,

Here is how my data is below:

1) So the calls total has to be first aggregated to State level from all Zones, then the state sum of calls has to be checked against 25, and report into a table if > 25. Resulting table will have month in first column, count of states with calls > 25 in second column

2) The result should still display all 12 months, even if there are months that do not have any states with > 25, it should still show that month, with a blank for state count

Thank you all.

Month	State	Zone	Calls
Jan	AK	1	10
Jan	AK	2	10
Jan	AK	3	5
Jan	AL	1	9
Jan	AL	2	9
Jan	AL	3	9
Jan	IL	3	15
Feb	AK	1	5
Feb	AK	2	5
Feb	AK	3	5
Feb	AL	2	50
Feb	MS	3	200
Feb	TN	2	10

[ziadm]

Hi

You have to generate a master calendar and include the missing dates ...

Link this master calendar to your fact Table CALLS

Generating Missing Data In QlikView

[swuehl]

If you already have all months in your data, try a straight table chart with dimension Month and as expression

=Sum( Aggr( If( Sum(Calls)>25, 1,0), Month, State))

Disable 'Suppress zero values' on presentation tab.

[#4]

Thank you.  It worked!  Really appreciated.  I may have few more questions as I build through the tables/charts. Great Help.