topic Re: Barchart with calculation on x-axis in QlikView

Barchart with calculation on x-axis

Anonymous — Fri, 15 Aug 2014 07:42:57 GMT

Hello,

I have the following scenario for which I can't seem to find a good solution:

For a multichannel report, I want to see how many people interacted with how many channels.

I want to represent this in a bar-chart, showing:

On the x-axis: the number of channels sequentially listed (eg: 1 - 2 - 3 - 4 - 5 - 6 - ...)

On the y-axis: the number of people that have interacted with the number of channels from the x-axis

So basically, the chart would answer a question like: "How many people interacted with exactly 4 channels".

I have no clue however on how to tackle this in a clean proper way.

Any help would be greatly appreciated.

Kind regards,

Christophe

Re: Barchart with calculation on x-axis

MK_QSL — Fri, 15 Aug 2014 08:14:30 GMT

Kindly Provide Sample Data...

You can use Class Function with Calculated Dimension.

Re: Re: Barchart with calculation on x-axis

Anonymous — Fri, 15 Aug 2014 09:16:56 GMT

I've attached it to this reply.

You can see 4 persons interacting with 4 channels.

Person 1 has 4 channels interacted with (although there are 5 total interactions)

Person 2 has 2 channels interacted with

Person 3 has 3

Person 4 has 4

Thanks in advance for looking into this

Re: Barchart with calculation on x-axis

qlikoqlik — Fri, 15 Aug 2014 09:33:49 GMT

Hi Chris

You can do this two ways

- you can create a new table and the original table will be connected via personid

- can add a new field to the existing table by left join

Please see below code for new table

Channels:LOAD Date,
Channel,
PersonIDFROM
C:\Users\padmap\Documents\QlikView\Examples\Channels.xlsx
(ooxml, embedded labels, table is Channel);

NoofChannels:

Load PersonID,Count(Channel) as NoofChannels Resident ChannelsGroup by PersonID;

Thanks n regards

PAdma

Re: Barchart with calculation on x-axis

Fri, 15 Aug 2014 09:47:32 GMT

Hi,

If you want for each Person, the no. of channels interacted with then

Dimension would be PersonID

Expression would be Count(Distinct Channel)

If you want for each Channel, the no. of persons interacted with then

Dimension would be Channel

Expression would be Count(Distinct Person ID)

Above two solutions are given as the post first and latest replies are put up differently.

Always remember Dimension is the context across which you want to show up a calculated value(Measure)

Thanks

Sri

Re: Barchart with calculation on x-axis

MK_QSL — Fri, 15 Aug 2014 09:53:02 GMT

Create a straight table or Bar Chart

Dimension = Calculated Dimension

=Class(Aggr(COUNT(DISTINCT Channel),PersonID),1)

Expression

Count(DISTINCT PersonID)

Re: Barchart with calculation on x-axis

Fri, 15 Aug 2014 10:13:56 GMT

Why not solve it with a scatter chart?

Then you are able to use these measures on both axis:

On the x-axis: the number of channels

On the y-axis: the number of people

If you do not have a dimension you can probably use a calculated dimension only containing a text string, like a space or something descriptive.

Re: Re: Barchart with calculation on x-axis

MarcoWedel — Fri, 15 Aug 2014 11:33:23 GMT

Hi,

I gree that doing this in the front end is more flexible than a aggregation at script level.

The class function doesn't seem to be necessary though:

hope this helps

regards

Marco

Re: Barchart with calculation on x-axis

Anonymous — Mon, 18 Aug 2014 07:08:02 GMT

Thanks, this worked for me !

Just wondering: how do you get rid of the autogenerated labels like "5 <= x <6" while all I need is "5".

Re: Barchart with calculation on x-axis

MarcoWedel — Mon, 18 Aug 2014 08:45:42 GMT

Just don't use the class function in this case.

Re: Barchart with calculation on x-axis

MK_QSL — Mon, 18 Aug 2014 09:07:05 GMT

Use the suggestion what Marco has given.. Absolutely right for your requirements..