topic Re: Associate chart problem in QlikView

Associate chart problem

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I actually have this chart

It represent the number of service request by Site.

I have 2 different chart, one pivot table and a combine bar chart.

My problem is that I have a calendar based on request date, and somes Site wasn't exist in 2014 for example so if I select 2014 my bar chart is not align with my pivot table and I dont know how to handle this.

I want to always display every Site on my pivot table like i did in my bar chart...

Ex 2014 --> PLA and SHA desappear

Thanks for your help

Re: Associate chart problem

sunny_talwar — Tue, 31 May 2016 15:50:52 GMT

Have you tried unchecking 'Suppress Zero Values' on the presentation tab for your pivot table?

Re: Associate chart problem

Wed, 01 Jun 2016 07:29:02 GMT

Hi, Thanks for you answer.

I try yes, I did it on the combine chart and it works, we can see that i have not value for 2 "sites".

But if I do the same in my pivot table, no way...

Re: Associate chart problem

sunny_talwar — Wed, 01 Jun 2016 11:12:01 GMT

Would you be able to share a sample?

Re: Associate chart problem

Wed, 01 Jun 2016 15:11:59 GMT

Sure, you can find it attached with this post

Re: Associate chart problem

sunny_talwar — Wed, 01 Jun 2016 15:29:16 GMT

Check this expression:

=If(Len(Trim([VIGS CATEGORY])) > 0, If(Count(ITEMID) = 0, 0.0001, Count(ITEMID)))

Re: Associate chart problem

Anonymous — Wed, 01 Jun 2016 15:37:53 GMT

=Dual(Count(ITEMID), count(TOTAL <[VIGS CATEGORY],SITE> {<YEAR=>} ITEMID))

Re: Associate chart problem

Wed, 01 Jun 2016 17:42:24 GMT

Hello Manuel,

Thanks for your help but it not work. Because using set analysis on year or month not allowed me to use my calendar, so I cant check my KPI for only 2014 for example. I have always the same result..

Re: Associate chart problem

Wed, 01 Jun 2016 17:48:27 GMT

WHat is the configuration of your check boxes on this case because if i take your expression its not better for me...
And if I open you file, its not works too...

Re: Associate chart problem

sunny_talwar — Wed, 01 Jun 2016 18:00:08 GMT

Check here:

Re: Associate chart problem

erjohnso — Wed, 01 Jun 2016 18:25:06 GMT

Change your expression in the pivot to:

=count(ITEMID)+sum({1} 0)

This way, all dimensions will always return at least a 0 no matter what criteria is selected.

Re: Associate chart problem

Wed, 01 Jun 2016 19:22:57 GMT

Hi,

Hum, think i miss something else! The problem is in the last screenshot: "Supress Zero Values"

If I check it, i have this:

And if I uncheck It works BUT I have a null row like:

Without this first row it will be perfect..

Re: Associate chart problem

Wed, 01 Jun 2016 19:26:15 GMT

Hi, it works, yes, can you explain why?

But I have another problem, the backgrond color of my "vigs category" dimension desapear if i select for example 2014... Do you know why?

Thanks a lot.

Re: Associate chart problem

sunny_talwar — Wed, 01 Jun 2016 19:52:19 GMT

Try this as your background color expression:

=if(Only({1} [VIGS CATEGORY]) = '3- Hospitality',rgb(102,194,150),

if(Only({1} [VIGS CATEGORY])='1- General Building Maintenance',rgb(141,170,203),

if(Only({1} [VIGS CATEGORY])='4- Office Services',rgb(255,217,47),

if(Only({1} [VIGS CATEGORY])='7- Other', rgb(252,115,98),

if(Only({1} [VIGS CATEGORY]) = '6- Food Services', rgb(187,216,84),

if(Only({1} [VIGS CATEGORY]) = '2- Pharma', rgb(231,138,210),

if(Only({1} [VIGS CATEGORY])='5- Janitorial', rgb(229,182,148),

if(Only({1} [VIGS CATEGORY]) = '8- Quotation', rgb(179,179,179)))))))))

Re: Associate chart problem

erjohnso — Wed, 01 Jun 2016 19:55:29 GMT

It works because we are calculating an expression for every dimension no matter what time period is selected.

The formatting for the color codes would need to be updated to include the {1} set identifier as well.

Re: Associate chart problem

Wed, 01 Jun 2016 20:53:51 GMT

Thanks to Both of you Eric and Sunny, it works very well now! 😃