21 Replies Latest reply: Jan 4, 2018 11:46 AM by Martha Parsons

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

• Re: ValueList in Pie Chart

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

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

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

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

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

• Re: ValueList in Pie Chart

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

• Re: ValueList in Pie Chart

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

Absolutely

• Re: ValueList in Pie Chart

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:

Then I have another table so I can do the comparison

BaseSites:

SITE as [Base Site]

Resident Data

Where Exists(SITE);

• Re: ValueList in Pie Chart

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

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

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

• Re: ValueList in Pie Chart

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

=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

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

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

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

• Re: ValueList in Pie Chart

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

)))

Removed three extra parenthesis from here

• Re: ValueList in Pie Chart

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

)

Martha

• Re: ValueList in Pie Chart

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

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.