HOW TO CREATE A CHART WITH TWO X-AXIS DIMENSIONS - BOTH DYNAMIC and ONE OF THEM SUBSEQUENT DAYS

[hhmcdama]

Hello friends,

can somebody please explain to me, how I can create the following chart in QlikView.

The hard part is to get the single bar (dimension) on the far left

The chart displays the monthly average cost of production on the left, and to the right the cumulitve daily costs of production

I have tried the following:

Dimension: 

 =ValueList('Month','Day')

Expression: 

= Pick(Match(ValueList('Month', 'Day'), 'Month', 'Day')
,vproductioncostmonth
,vproductioncostday)

Now this works only partly,  it give's me exactly two bars next to each other, but it doesn't treat days as an dynamic dimension, thus if I have selected more than one day I would receive the sum of the costs for all of the days but not day 1, day2, day3... next to each other... 😕

Can somebody help me with this? 

Thanking in advance!

😃

[sunny_talwar · Accepted solution]

Instead of ValueList dimension, create an Island table in the script like this

Dim:
LOAD * INLINE [
Dim
1
2
];

Now use this as you dimension

Pick(Dim, 'Monat', Day)

Here I have not used single quotes around Day field... meaning Day is an actual field where as Monat is hard coded value. Expression

= Pick(Dim
,vproductioncostmonth
,vproductioncostday)

[hhmcdama]

Thanks man! People like you make this world a better place! So greatful!

[hhmcdama]

Hello my friend,

I would like to change the color of the first bar (Monat). But I'm having trouble setting the conditions since we sort of created two dimensions in one. 

I tried the following, which obviously failed:

if(Dim='Monat',RGB(0,0,0),RGB(197,188,164) ) 

pick(match(Dim,'Monat',Day),if(Dim='Monat',green()))

Thanking in advance!

😃

[sunny_talwar]

Try this for color expression

If(Dim = 1, RGB(0,0,0), RGB(197,188,164))

[hhmcdama]

Hmm, sadly didn't work 😕

I tried:

If(Dim = 1, RGB(0,0,0), RGB(197,188,164))
If(Dim = '1', RGB(0,0,0), RGB(197,188,164))
If(Dim = 'Monat', RGB(0,0,0), RGB(197,188,164))

but no luck..

[sunny_talwar]

I don't expect the third one to work, but I would have expected the first 2 to have worked.... Would you be able to share a sample where you can show this issue?

[hhmcdama]

Hello Sunny_talwar,

here is the sample .qvw. Maybe you have another idea of how I could solve this issue?

Thanking in advance!!

Best,

Malte

[sunny_talwar · Accepted solution]

What is the issue here?

[hhmcdama]

Hello =),
thanks awesome! This works. I inserted the condition in the colors tab and not in the expression-background-and-color tab.

Thanks so much! Have a great day!

😃