Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mp802377
Creator II
Creator II

ValueList in Pie Chart

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

1 Solution

Accepted Solutions
mp802377
Creator II
Creator II
Author

That didn't work, I got an error. But I did find the solution and wanted to post it here.

pick(

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

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

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

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

)

Thank you so much for your help. I wouldn't have the answer without your help.

Martha

View solution in original post

21 Replies
sunny_talwar

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

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

YoussefBelloum
Champion
Champion

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 ?

mp802377
Creator II
Creator II
Author

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.

sunny_talwar

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)),

)))

mp802377
Creator II
Creator II
Author

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 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.

mp802377
Creator II
Creator II
Author

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.

pradosh_thakur
Master II
Master II

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

Learning never stops.
sunny_talwar

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...

pradosh_thakur
Master II
Master II

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.

Learning never stops.