-
1. Re: Two Simple Data format questions
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 MurakamiSep 29, 2016 10:16 AM (in response to Charles Foley)
-
3. Re: Two Simple Data format questions
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 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 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.