topic Re: Mixing static and summed data in QlikView

Mixing static and summed data

Tue, 03 Dec 2013 20:36:26 GMT

Hi All

I am brand new to QlikView, and have just seen the getting started webinar. I have a general question, I have two tables. One holds target counts for some specific criteria. The other tables holds records for the item which is being counted. Both tables have some filtering criteria in common e.g region. The question is, can I create a vertical chart where each region displays two bars, one for the target count in the first table, and one for the actual count based on the sum of records in the second table?

Thx,

Hamayoun

Re: Mixing static and summed data

swuehl — Tue, 03 Dec 2013 21:30:54 GMT

Probably yes, but it would be helpful if you could describe your data model (the two tables) a bit more detailed.

You'll need to link your two tables by a key field (as you probably know, a field with common name in both tables).

Please note that linking the tables by more than two fields with common names introduces a syn key table, and is probably not working as intended, that's why it's important to know more about your data.

Re: Mixing static and summed data

Tue, 03 Dec 2013 21:56:24 GMT

Ok, let me clarify swuehl. Table 1, lets call it 'Goals', has the following fields:

Year

Type

Region

Target Count

It stores the target counts of successfully completed Opportunities per year, per type, per region. Table 2, the 'Opportunities' table, has the following fields:

Year

Type

Region

Status - indicates if the opportunity has successfully completed

So my requirement is that on one chart, I can display side-by-side two vertical bars per type, one bar being the Target Count from the Goals table, and the other bar being the sum of the corresponding Opportunities where the Status indicates that the Opportunity is successful.

Thx,

Hamayoun

Re: Mixing static and summed data

swuehl — Tue, 03 Dec 2013 23:21:45 GMT

I would probably either join or concatenate the two tables into one table (I think I would go for a join (or mapping)).

Something like

LOAD

Year,

Type,

Region,

Target

FROM ...;

LEFT JOIN LOAD

Year,

Type,

Region,

Status

FROM ...;

This should result in a table with fields Year, Type, Region, Target and Status (maybe I am misunderstanding your setting, maybe you have multiple opportunities and want to sum something you haven't mentioned so far?)

Then you can create a chart and show the Target count:

=sum(Target)

and the target count only for the successful status:

=sum(if(Status='Success',Target))

=sum({<Status = {Success}>}Target)

Re: Mixing static and summed data

Wed, 04 Dec 2013 16:33:02 GMT

Still not sure if you understand what I need, so let me do this explicitly, sorry I didn't do it like this in the first place.

So here is the data for the Goals table:

Year	Type	Region	Target
2013	T1	R1	10
2013	T1	R2	11
2013	T2	R1	12
2013	T2	R2	13

And here is the data for Opportunities table:

OppID	Year	Type	Region	Status
Opp1	2013	T1	R1	Won
Opp2	2013	T1	R2	Closed
Opp3	2013	T2	R1	In Progress
Opp4	2013	T2	R2	Won
Opp5	2013	T1	R1	Closed
Opp6	2013	T1	R2	In Progress
Opp7	2013	T2	R1	Won
Opp8	2013	T2	R2	Closed
Opp9	2013	T1	R1	In Progress
Opp10	2013	T1	R2	Won
Opp11	2013	T2	R1	Closed
Opp12	2013	T2	R2	In Progress
Opp13	2013	T1	R1	Won
Opp14	2013	T1	R2	Closed
Opp15	2013	T2	R1	In Progress
Opp16	2013	T2	R2	Won
Opp17	2013	T1	R1	Closed
Opp18	2013	T1	R2	In Progress
Opp19	2013	T2	R1	Won
Opp20	2013	T2	R2	Closed

I want a chart that shows, per type, Target Count vs Sum of Won Opportunities. For the data above, it would like look this.

So can this be done in QlikView?

Thx,

Hamayoun

Re: Mixing static and summed data

angelaecheverri — Wed, 04 Dec 2013 16:56:46 GMT

rirst thing you need to do is load both tables(Goals and Opportunities)

then both tables must be linked by a Key.

Like this

Goals:

Load

Autonumber(Year&'-'&Type&'-'&Region) as Key.Main,

Year as Year_Goals,

Type as Type_Goals,

Region as Region_Goals,

Target as Target_Goals

From

xxxx

Opportunities:

Load

Autonumber(Year&'-'&Type&'-'&Region) as Key.Main,

OppID,

Year,

Type,

Region,

Status

From

xxxxx

then In the chart objet

Dimension Year

Expresion 1 sum(Target) ---> it will sum target

Expresion 2 count({<Status = {'Won'}>} Status) --> it will count registers which status is won.

Re: Mixing static and summed data

Wed, 04 Dec 2013 16:59:03 GMT

Thanks Angela!