topic ValueList in Pie Chart in QlikView

ValueList in Pie Chart

mp802377 — Wed, 27 Dec 2017 15:32:37 GMT

Hello,

I am getting a 'No data to display' with what I have below. I have three categories and I need to do a count, not a sum. I first started with Pick, but looking at discussions, I saw something like this.

Dimension

=(ValueList('We Win','Matched','They Win'))

Expression

=IF(ValueList('We Win','Matched','They Win') = 'We Win',

Count(Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE))<0,

=IF(ValueList('We Win','Matched','They Win') = 'Matched',

Count(Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE))=0,

=IF(ValueList('We Win','Matched','They Win') = 'They Win',

Count(Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE))>0,

)))

Thank you,

Martha

Re: ValueList in Pie Chart

sunny_talwar — Wed, 27 Dec 2017 15:36:19 GMT

You expression is using double aggregation without an Aggr() function

Count(Min()) won't work... what exactly are you trying to do?

Re: ValueList in Pie Chart

YoussefBelloum — Wed, 27 Dec 2017 15:37:42 GMT

Hi,

why don't you simply do this:

on the dimension part, just put: =Dimension

on the expression part: if( Dimension ='value', count( {<Dimension = {'Value'}>} field

did you try this ?

Re: ValueList in Pie Chart

mp802377 — Wed, 27 Dec 2017 15:48:31 GMT

I need to have a pie chart that counts the expression based on the total of the expression. They want a pie chart that reflects how many times we were below zero, how many times were at zero, and how many times we went above zero. The expression is: (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)).

So, if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) = 0 than the count of that would reflect 'Matched' and the count,

if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) < 0 than the count of that would reflect 'We Win' and the count,

if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) > 0 than the count of that would reflect 'They Win' and the count.

Re: ValueList in Pie Chart

sunny_talwar — Wed, 27 Dec 2017 15:52:22 GMT

Do you have a match_id or Match_Number like field where you are checking these conditions against?

May be try this

=If(ValueList('We Win','Matched','They Win') = 'We Win',

Sum(Aggr(If(Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) < 0, 1, 0), MatchID)),

=If(ValueList('We Win','Matched','They Win') = 'Matched',

Sum(Aggr(If(Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) = 0, 1, 0), MatchID)),

=If(ValueList('We Win','Matched','They Win') = 'They Win',

Sum(Aggr(If(Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) > 0, 1, 0), MatchID)),

)))

Re: ValueList in Pie Chart

mp802377 — Wed, 27 Dec 2017 15:57:15 GMT

So, if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) = 0 than the pie of that would reflect 'Matched' and the count,

if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) < 0 than the pie of that would reflect 'We Win' and the count,

if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) > 0 than the pie of that would reflect 'They Win' and the count.

Re: ValueList in Pie Chart

mp802377 — Wed, 27 Dec 2017 16:55:44 GMT

Hello, I do not have a MatchID to compare

The end user will put in there selections using the listboxs. In the pie chart, they want to see how many times using the expression ( (Min({$<SITE={$(=[Base Site])}>} FARE) - Min( FARE))/ Min({$<SITE={$(=[Base Site])}>} FARE)) to find out how many times when the fare of Base Site - the fare of SITE is AT, Equal, or Below

They want to see how many times we beat the price. The Base Site is comparing against everyone else. how many times are we less than them? For example, if we were comparing electronics and someone selects iPod in the list box the Base Site is us and it is comparing against everyone else. You want to know how many times did we beat the other competitors, how many times did we match, and how many times did they beat us.

if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) = 0 than the pie of that would reflect 'Matched' and the count of how many =0,

if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) < 0 than the pie of that would reflect 'We Win' and the count of how many are less than 0,

if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) > 0 than the pie of that would reflect 'They Win' and the count of how many greater than 0.

Re: ValueList in Pie Chart

pradosh_thakur — Wed, 27 Dec 2017 18:25:33 GMT

We dont know your data model so it will be hard for us. So please share as much info as you can

Try this

no dimension

expression 1 =sum(if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) = 0 ,1,0)

expression 1 label - matched

expression 2 =sum(if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) < 0 ,1,0)

expression 2 label - we win

expression 3 =sum(if (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) > 0,1,0)

expression 3 label - they win

you may have to provide some field where you are selecting i pod etc to make it work.

share the screenshot of the output.

regards

Pradosh

Re: ValueList in Pie Chart

sunny_talwar — Wed, 27 Dec 2017 18:32:14 GMT

I don't think this will work pradosh_thakur‌ as your have Sum(If(Min(.... Sum over Min without Aggr() is not going to work here...

Re: ValueList in Pie Chart

pradosh_thakur — Wed, 27 Dec 2017 18:42:00 GMT

Didn't realize that until you said. tried with [filedname] > some value and suggested the approach. I guess we need the field in which he is selecting the electonice/i-Pod.

Re: ValueList in Pie Chart

sunny_talwar — Wed, 27 Dec 2017 18:43:36 GMT

Absolutely

Re: ValueList in Pie Chart

mp802377 — Wed, 27 Dec 2017 19:10:53 GMT

This is an example of the data file coming in. Normally the data would come through a database, but I put it in a csv so you could see it.

Data:

SITE,OBJECT,FARE

Our Company,iPad Mini,249.99

Our Company,iPad 10.5,649.99

Our Company,Samsung Galaxy Tab,599.99

Our Company,Lenovo Laptop Flex Gen 5,699.99

Our Company,Logitech Headset,59.99

Competitor One,iPad Mini,248.99

Competitor One,iPad 10.5,639.99

Competitor One,Samsung Galaxy Tab,549.99

Competitor One,Lenovo Laptop Flex Gen 5,625.99

Competitor One,Logitech Headset,74.99

Competitor Two,iPad Mini,259.99

Competitor Two,iPad 10.5,649.99

Competitor Two,Samsung Galaxy Tab,648.99

Competitor Two,Lenovo Laptop Flex Gen 5,709.99

Competitor Two,Logitech Headset,65.99

Competitor Three,iPad Mini,249.99

Competitor Three,iPad 10.5,649.99

Competitor Three,Samsung Galaxy Tab,599.99

Competitor Three,Lenovo Laptop Flex Gen 5,689.99

Competitor Three,Logitech Headset,49.99

Then I have another table so I can do the comparison

BaseSites:

LOAD Distinct

SITE as [Base Site]

Resident Data

Where Exists(SITE);

Re: ValueList in Pie Chart

mp802377 — Wed, 27 Dec 2017 19:23:39 GMT

And there also may not be a case where OBJECT is selected. Because they also want to see an overall us vs them. That is why I put OBJECT in a list box and not making it required.

Thank you so much for help on this!

Martha

Re: ValueList in Pie Chart

pradosh_thakur — Wed, 27 Dec 2017 19:34:21 GMT

Are you sure this is what the expression iMin({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) = 0 can you tell the exact requirement with respect to the data .

Re: ValueList in Pie Chart

sunny_talwar — Wed, 27 Dec 2017 19:45:00 GMT

Base Site will have multiple values or is this always one selected value?

Re: ValueList in Pie Chart

mp802377 — Wed, 27 Dec 2017 19:48:18 GMT

The original expression for bar charts is (Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)). I was trying to put it as if that expression = 0 then with the count.

In the example above, the iPad mini (if chosen) then pie would be split in three ways because there is one that is greater than our companies price, one that is less, and another that matches.

The iPad 10.5 would reflect a pie that has one competitor less ('They Win'), but the others match ('Matched')

Base Site will always have one selected value. It may be us, it may be another company they want to do comparisons against.

Re: ValueList in Pie Chart

sunny_talwar — Wed, 27 Dec 2017 20:06:13 GMT

Got it, how about this

=If(ValueList('We Win','Matched','They Win') = 'We Win',

Sum(Aggr(If(Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) < 0, 1, 0), OBJECT)),

=If(ValueList('We Win','Matched','They Win') = 'Matched',

Sum(Aggr(If(Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) = 0, 1, 0), OBJECT)),

=If(ValueList('We Win','Matched','They Win') = 'They Win',

Sum(Aggr(If(Min({$<SITE={$(=[Base Site)}>} FARE) - Min(FARE)) > 0, 1, 0), OBJECT)),

)))

Re: ValueList in Pie Chart

mp802377 — Wed, 27 Dec 2017 20:44:16 GMT

I tried that and got an error.

I removed the = and still got this: It looks like it is erroring out at the 0

Re: ValueList in Pie Chart

sunny_talwar — Wed, 27 Dec 2017 20:51:46 GMT

You seem to not have OBJECT as a field... what is the field called? You need to replace OBJECT with whatever field you have in your dashboard that tells you iPhone, iPad etc... also remove '=', just like you did

Re: ValueList in Pie Chart

mp802377 — Wed, 27 Dec 2017 21:03:42 GMT

The field name is OBJECT. Is OBJECT a used name that I shouldn't be using?

I noticed it is erroring out after the 1