topic Re: Showing one datapoint for "several selections"? in QlikView

Showing one datapoint for "several selections"?

Mon, 19 Sep 2011 20:28:34 GMT

I had a really difficult time to Google this but consider that I have the following date:

Date	Goals	Subcategory	Score
2011-01-01	Math	Teamwork	2
2011-01-01	Math	Leadership	5
2011-01-01	Math	Analytics	6
2011-01-01	English	Teamwork	12
2011-01-01	English	Leadership	9
2011-01-01	English	Analytics	14
2011-06-01	Math	Teamwork	4
2011-06-01	Math	Leadership	3
2011-06-01	Math	Analytics	6
2011-06-01	English	Teamwork	10
2011-06-01	English	Leadership	7
2011-06-01	English	Teamwork	16

Obviously the real data has much more datapoints but the same amount of fields (four fields), I want to be able to show each individual score based one selection and I do not know how to achieve this.

I thought that I would make a diagram with Date, Goals and Subcategory as dimensions and Score as an expression, however this does not seem to work?What I am doing wrong and how would I be able to achieve this?

Showing one datapoint for "several selections"?

Mon, 19 Sep 2011 20:47:43 GMT

Did you do sum(Score) or just Score?

Showing one datapoint for "several selections"?

Mon, 19 Sep 2011 20:56:14 GMT

No, I do not want to sum score... basically if I make a barchart and make the following selection

Date: 2011-01-01

Goals: Math

Subcategory: Teamwork, Leadership & Analytics

I would want this selection to show three different scores (2, 5 and 6 on one bar each)

Showing one datapoint for "several selections"?

swuehl — Mon, 19 Sep 2011 20:57:44 GMT

The resulting table looks fine too me, except that

on 2011-06-01, you have multiple records for English and Temwork with values 10 and 16.

You won't get individual lines for them.

You could add Score to your dimensions too and add an expression

and hide this expression in presentation (talking about a straight table), but this will only work for unique combinations of Score, Date, Goals, Subcategory.

So maybe you could add a record no to the table and hide this in presentation tab of the properties?

Regards,

Stefan

Showing one datapoint for "several selections"?

Mon, 19 Sep 2011 21:03:51 GMT

Yes the last row should be analytics, sloppy copying

So yeah, I find that each line has an unique identified with the combination of Date, Goals and Subcategory... but I still don't manage the data to show this way.

I would make a barchart with date, goals and subcategory as three individual dimensions?

And then add the Score as a expression?

So if I selected all dates (2), all goals (2) and all subcategories (3) I would get 12 datapoints... that's what I am after

Showing one datapoint for "several selections"?

swuehl — Mon, 19 Sep 2011 21:16:40 GMT

I believe the standard behaviour of a bar chart allows 3 dimensions and 1 expression only in stacked style.

So you will probably only get a stacked bar with stacked subcategories, right?

Is that your problem?

Showing one datapoint for "several selections"?

Mon, 19 Sep 2011 21:42:50 GMT

Yeah, that explains it! I changed it to stack bars and it show perfectly, however this is not how I like it... I like it as a histogram (like an inflation rate chart over time?)...

Is that possible and how?

Showing one datapoint for "several selections"?

swuehl — Mon, 19 Sep 2011 21:47:04 GMT

Could you maybe reduce the three dimensions to up to two by concatenating e.g. Goals and Subcategories, creating a new dimension?

Showing one datapoint for "several selections"?

Mon, 19 Sep 2011 21:49:50 GMT

How would I do that the best way? I could combine subcategory and goal as "one selection", ie always requiring a selection of both of those...

Re: Showing one datapoint for "several selections"?

swuehl — Mon, 19 Sep 2011 22:00:33 GMT

Hm, not sure what you mean..

Please have a look at attached (and at the script part for concatenation).

Maybe like this?

Showing one datapoint for "several selections"?

Mon, 19 Sep 2011 22:29:24 GMT

I have personal edition so I cannot really see it but I can see the code:

LOAD Date,

Goals,

Subcategory,

Score,

Goals &'-'& Subcategory as GoalSubs

However, I can not see the structure of your graph, maybe you can take a printscreen of dimensions and expressions? Sorry if I am asking to much

Showing one datapoint for "several selections"?

swuehl — Mon, 19 Sep 2011 22:38:08 GMT

Sure:

this is my script:

LOAD Date,

Goals,

Subcategory,

Score,

Goals &'-'& Subcategory as GoalsSubs

FROM

[http://community.qlik.com/thread/35702?tstart=0]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 14))

));

Then I did just bar chart with

dimensions Date and Goalssubs and Expression Score

It looks like this:

Showing one datapoint for "several selections"?

Mon, 19 Sep 2011 22:45:48 GMT

Another way to get the same output as shown by sweuhl is to create a calculated dimension of Goals and Subcategories in the chart dimensions.

Showing one datapoint for "several selections"?

Mon, 19 Sep 2011 22:46:27 GMT

Thank you very much! I managed to solve it and you been very patient and helpful, big star for you my friend!

Re: Showing one datapoint for "several selections"?

anuradhaa — Mon, 11 Feb 2013 06:53:38 GMT

Do you know how to show the some in 1/1/2011 (48) as total and other date in this chart?

Re: Showing one datapoint for "several selections"?

Anonymous — Thu, 14 Mar 2013 05:36:12 GMT

Good show stephen.

Can i do this without concatenation?

I want the same scenario like the attached screen.

" Sample_1 is the sample given by client "

" QV_Result is the report that i am getting in QV. "

I very well know i am doing a small mistake but i am not able to fix that.

Can you help me here?

Rgds,

Nandha

Re: Showing one datapoint for "several selections"?

Anonymous — Thu, 14 Mar 2013 05:40:46 GMT

Good show stephen.

Can i do this without concatenation?

I want the same scenario like the attached screen.

" Sample_1 is the sample given by client "

" QV_Result is the report that i am getting in QV. "

I very well know i am doing a small mistake but i am not able to fix that.

Can you help me here?

Rgds,

Nandha

Attachments:

Sample_1.gif (83.2 K)
QV_Result.gif (107.3 K)