5 Replies Latest reply on Sep 29, 2016 10:42 AM by David Li

    Two Simple Data format questions

    Charles Foley
    Charles Foley Sep 29, 2016 9:59 AM

      I have two data sets. 

       

       

      1.

      One is a cartesian matrix of our customers and every possible product they have.  It also has their revenue for the products they purchase. if they didnt purchase a product, the revenue value is black.

       

      The issue i am having.  I would like to do a gradient color scale for revenue.  Howver, the null value is always placed in the middle of that gradient color scale, completely making the color scale useless. 

       

      I want the null values to be set to white/gray.  And the rest of the my scale to be a green gradient from light to dark green base don how much revenue they have. Ive been stuck for weeks, no dice.

       

       

      2.  I would simply like to take the max value of my order date and put it on my measure value shelf.  it will not let me put this on my measure value shelf and only allows me to do a count

      • 169 Views
      • Tags:
        • 1. Re: Two Simple Data format questions
          David Li
          David Li Sep 29, 2016 10:11 AM (in response to Charles Foley)

          Hi Charles! For #1, try using ZN(SUM([Revenue])) as your formula. That should convert the nulls to zeros. If not, you may have to throw in LOOKUP() inside ZN(). Then, you can set zero to be white on your color scale.

           

          For #2, what are you trying to do with the order date, exactly? If you're displaying values using Measure Names/Values, note that you can only display numeric values. Dates and strings aren't allowed.

          • 2. Re: Two Simple Data format questions
            Shinichiro Murakami
            Shinichiro Murakami Sep 29, 2016 10:16 AM (in response to Charles Foley)

            Could you share your sample workbook as packaged?

             

            1. Usually I see the color on blank as white.  I want to see your dataset.

             

            2. [Max date]

            {fixed:max([date])}

             

            Then you can use the field [Max date] as dimension.

             

             

            Thanks,

            Shin

            • 3. Re: Two Simple Data format questions
              Charles Foley
              Charles Foley Sep 29, 2016 10:36 AM (in response to David Li)

              Hey David, 

               

              The one issue with converting the Nulls to 0's is that i actually wanted to subtract the revenue from my average customer revenue to get a difference. 

               

              for the dates, i want to create a cross tab that could be exported to excel with the max and minimum order date for each product by customer.   The reason i wanted to put it in my values shelf, is that way i can put my measure names on my columns and have it be a clean export to excel for my end user where they wouldnt have to manipulate anything

              • 4. Re: Two Simple Data format questions
                Charles Foley
                Charles Foley Sep 29, 2016 10:38 AM (in response to Shinichiro Murakami)

                Shinichiro,

                 

                the color is white in your case, because there is no row at all for that corresponding customer and product matchup. in my case, there is a row.

                 

                additionally , i would like the niice neat box with a black border rather than just seeing the the blank worksheet when there is no revenue.

                • 5. Re: Two Simple Data format questions
                  David Li
                  David Li Sep 29, 2016 10:42 AM (in response to Charles Foley)

                  Charles, as Shinichiro said, can you share a packaged workbook? That would help a lot.

                   

                  One option you may have is to use the ZN() trick on the difference calculation, but use it only on the color mark. That'll solve the issue without showing a 0 where it should be null.

                   

                  Also, for the dates, you should probably force them into the headers by putting the max/min calc on the rows shelf and then making them discrete. Again, I'd need to see how your workbook is laid out to know how to formulate the calc.