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

    ValueList in Pie Chart

    Martha Parsons

      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

          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
              Martha Parsons

              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

                  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
                      Martha Parsons

                      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

                          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

                              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
                                  pradosh thakur

                                  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
                                    Martha Parsons

                                    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
                                        Martha Parsons

                                        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

                                          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

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

                                              • Re: ValueList in Pie Chart
                                                Martha Parsons

                                                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

                                                    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
                                                        Martha Parsons

                                                        I tried that and got an error. error one.PNG

                                                         

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

                                                         

                                                        error two.PNG

                                                          • Re: ValueList in Pie Chart
                                                            Sunny Talwar

                                                            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
                                                                Martha Parsons

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

                                                                 

                                                                error three.PNG

                                                                 

                                                                I noticed it is erroring out after the 1

                                                                error four.PNG

                                                                  • Re: ValueList in Pie Chart
                                                                    Sunny Talwar

                                                                    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

                                                                    Capture.PNG

                                                                      • Re: ValueList in Pie Chart
                                                                        Martha Parsons

                                                                        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

                                            • Re: ValueList in Pie Chart
                                              Youssef Belloum

                                              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
                                                  Martha Parsons

                                                  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.