Hoping someone can help me with this please?
I am trying to use the idea in this blog post (and others) on how to create bar charts with shapes:
The key difference between the example and my data is:
- I don't want to report a sum of sales (or anything else), rather a count of a number of unique catalogue numbers that meet a set of criteria (should be similar I thought)
- There will be a small number of incidents of each item (maximum 7) rather than the large number of items that would apply in the example (so no need to divide by 17,500, I am happy to have one shape for each person). I thought this might mean I don't need the Index formula, just a Count of the number of items?
My data is confidential at this stage, so I have anonymised it in the attached workbook. The workbook shows the result I am expecting in both a cross tab, and a simple shapes diagram where the size of the circle shows the number of items. The problem is that readers find the circles hard to interpret, so a separate pictogram for each item would be much simpler.
The third worksheet shows exactly what I am looking for in terms of format, except:
- I'd love to reduce the gap between the individual pictograms to make the columns narrower (and potentially also increase the size of the figures a little) so that the figures touch up against each other. I know how to change the size, but can't work out how to reduce the gap between the individual figures - every time I currently increase the size, the gap gets bigger?
- The results are wrong. Because my data has multiple line items for each catalogue number, I have used the formula CountD(CatNo) to provide a count of each unique CatNo that meets the selection criteria in the filters. This works perfectly in my cross tab and shapes version of the report, but for some reason the pictogram version of the report doesn't give the same result (I suspect it is ignoring the CountD part of the formula, and returning a line for each incident of the CatNo, but am not sure).
In the fourth version of the worksheet I have tried to replicate the formula from the BlogPost and adapt it for the fact I am not using a Sum of a $amount in the formula, but it seems to return a different answer again.
Can anyone help me:
- work out how to get the right answer in the pictogram report?
- tell me how to narrow the gap between the individual stick figures?
- Ideally the four columns (Category A, etc.) would be the same width (narrower than the version I have done) to allow for up to 7 stick figures as per the third worksheet, not the different widths as per the fourth worksheet
Thanks in advance