Hi-
I have attached a workbook and a picture.
I am trying to get a sum countD of ID's that fall within a certain bin. Currently, I am having a problem because it is taking each unique ID and counting it as 1...this is what I want, however I need them to sum to a total. For example: 15+ bin should total 3 and a label should show that.If you click on each vertical bar, it is just stacking on top of each other individual ID's
Nov 26, 2012 8:23 AM
Hi,
Attached is one way to go about this. Instead of drawing 745 marks (that are stacked up into 4 bars), I set up the calculations to draw 4 marks (one per bar). Here's what I did:
1. Create a calculated field called WS CountD that is
IF FIRST()==0 THEN
WINDOW_SUM([CountD],0,IIF(FIRST()==0,LAST(),0))
END
This table calculation will have its Compute Using set to ID and is set up to only return one value per partition.
2. In the view, uncheck "Ignore in Table Calculations" on the blue FrqCountBins pill. This lets the WS CountD calc partition on FrqCountBins, so we get four results.
3. Use WS CountD in place of CountD on Rows and set the Compute Using to ID. This gets us the four bars with a bunch of Null values (the Null warning in the corner).
4. Ctrl+drag the WS CountD over to the Filters Shelf and set to filter for Non-Null values.
5. Click on the Label Shelf drop-down to turn on Mark Labels.
Cheers,
Jonathan
Jonathan-
Seems like the problem I keep running into is maybe because of how I binned the records? I am trying to get now a stacked bar chart to total the 745 breaking it down by bin. It is showing each individual record instead of grouping all the ID's that belong in that bin? Thoughts
Nov 26, 2012 9:19 AM
Hi Ethan,
You can't filter on FrqCountBins because it is a discrete aggregate, with aggregates you can only filter on continuous values.
I'm not sure what you're running into with a stacked bar, I'm guessing you're still using ID on the level of detail and not using a table calc to have the aggregation happen over the FrqCountBins. I've set up a stacked bar in the attached, the tricky bit was playing with the sort on ID so the bars are in the right order.
Jonathan
This is what I want, thanks and yes it was what you thought was the issues.
I keep facing issues though maybe because how i set something up originally. If I right click on lets say the "4-6 visit" within the FrqCountBins, and try to exclude the entire bin (650 ID's) it only takes away 1 value and drops to 649 ID's. I want to exclude all of them in that bucket.
Is there a better way to set up the calculation to put the ID's respectively in bins or groups?
Nov 26, 2012 10:22 AM
The reason why it's only excluding 1 value is that you are excluding based on ID (that's what you'll see on the Filter Shelf) and not FrqCountBins, and there is only one mark being drawn for each bar that only has one ID associated with it. To exclude those, my suggestion is to have a separate calculated field that returns 1,2,3, etc. so it can be a continuous measure, and then filter on that instead of using right-clicking in the view. It's kludgy but it gets the job done.
Jonathan