topic Bar Diagram - Each bar another function in QlikView

Bar Diagram - Each bar another function

Sat, 01 Aug 2015 15:46:15 GMT

Hello,

i want to create a bar diagram.

This diagram should contain of multiple BARS. Each BAR representing a controlnumer.

Behind each controlnumber should be a function.

I can model it within the loading script, but when i click on a bar then i dont see the underliing filters in the selection overview.

I can make it work for 1 Bar per diagramm like the following.

I create a bar diagram and add a dynamic dimension for instance:

=if(hair_color='blond','Controlnumber1')

Now the problem is - i try to add other controlnumbers. The if / else does not work for that.

e.g.:

=if(hair_color='blond','Controlnumber1',

if(hair_color='blond' AND eyes='blue' ,'Controlnumber2'))

(base formula = count(distinct personids)

The problem about this is, that controlnumber 2 gets not shown because it is a subset of controlnumber 1. QlikView seems to only allow to display the bars if they are not overlapping.

But my controlnumbers DO overlap.

So how can i do this?

Please keep in mind, that i want to maintain the connection to other tables and that when i click on a bar, i still want to see the conditions / filters for this bar (which works for a single bar).

Can anyone help me out with this?

Re: Bar Diagram - Each bar another function

swuehl — Sat, 01 Aug 2015 15:55:37 GMT

Maybe it's just enough to order your conditions subset first:

=if(hair_color='blond' AND eyes='blue' ,'Controlnumber2',

if(hair_color='blond','Controlnumber1')

)

Or do you want to the records to be counted in the subset as well as in the superset?

Then you need to link the records to both sets in the script.

Re: Bar Diagram - Each bar another function

Sat, 01 Aug 2015 16:07:05 GMT

Hello,

thanks a lot for the hint. Actually now after appliing your solution it still not works as i want it to work.

And yes, i think i want the records to be counted in the subset and the superset. Actually i would really prefer

if those BARS / SETS are completely independent from each other.

After appliing your solution i get:

5 blond blue

10 blon.

((In reality i should get 15 blond))

So basically - i think your second part should be my solution:

"Then you need to link the records to both sets in the script."

Unfortunately i do not know how to do this. Could you maybe give me a code snippet? Or explain a bit further - i am pretty much beginner level in QlikView.

Re: Bar Diagram - Each bar another function

antoniotiman — Sat, 01 Aug 2015 16:20:03 GMT

Hi Daniel,

maybe You can use ValueList() .

See attachment.

Regards,

Antonio

Re: Bar Diagram - Each bar another function

swuehl — Sat, 01 Aug 2015 16:20:19 GMT

Basically, you create a table with a n:m relation between personids and control numbers, something like

ControlNumber:

LOAD personids,

'ControlNumber1' as ControlNumber

FROM YourTableWithPersonIDsHairColorAndEyes

WHERE hair_color = 'blond';

CONCATENATE (ControlNumber)

LOAD personids,

'ControlNumber2' as ControlNumber

FROM YourTableWithPersonIDsHairColorAndEyes

WHERE hair_color = 'blond' AND eyes='blue';

Re: Bar Diagram - Each bar another function

Sat, 01 Aug 2015 16:44:40 GMT

Thanks a lot! That made it work for me! Exactly how i want it!!!! Great help apreciate it!!!

Also thanks to Antonio - tried out the solution - could not make it work like i want to with the ValueList. Maybe there is a way to use it - but i could not figure it out.