Count up fields base on visual cues or values in another chart?

[#1]

Hi All,

What I am looking for, and  I may just be overcomplicating this, is I have a pivot chart that have dimensions for Month/Day of week (mon-fri) and week number (1-5 weeks per month) and for expressions is just easy SUM(field).  For each expression i setup a visual cue to highlight when a sum >5 in red.  What I need to be able to do, and this should likley be done in another chart, is count up for any given dimension the # of sums >5.  Attached is a picture of my chart and one of the expression fields.

What I need to be able to see in another chart is

Month           total count     by dimension

January          6                    rolled full month up (all red values)

January          3                    filtered to Friday (6/7/6 values)

This data will obiously be run for larger date ranges so i want to be able to move the Month dimension to 2nd place and be able to roll this data to DoW only and then by month etc..

Maybe this can be done in the same chart even, but I'm having trouble figuring out how to put it all together.

Thanks,

Stan

[Anonymous]

Hi,

try this expression:

sum(if(sum( [530 to 545]) > 5, 1 )

However this will only return a maximum of 1 per row when your Pivot is fully expanded. If you want to have a count iterated over special dimensions you can use:

sum( TOTAL <[Month Name]> if(aggr(sum(TOTAL <[Month Name]>[530 to 545]), [530 to 545]) > 5, 1 ))

Total will ignore the diagram dimensions and sum up for the dimension enclosed in <>.

for your second question you could use and expression like:

sum( TOTAL <[Month Name]> if(aggr(sum(TOTAL <[Month Name]> { < DoW = {Friday} >}  [530 to 545]), [530 to 545]) > 5, 1 ))

Set analyis will only count those DoW which are Friday.

Hope I understood your requirement adequately.

Best regards

Stefan

[#3]

I tried these functions in both the main chart and in a separate one.. they don't seem to be working as planned or i totally missed something.

I attached a trimmed down file of just this info im working with to look at.

[Anonymous]

Hi,

corrected the formulas, without test data it's not that easy

See attached qvw.As I said I'm not 100 % about your requirement so please clarify if this doesn't meet your requirement.

Best Regards

Stefan

[#5]

Thank you Stefan.. it is helpful, but it's still not calculating right for what I need.

For the application you sent back, in the table you created.

If you choose Month Name of January, DoW of Friday you'll get 3 red blocks (6,7,6).  I need a column that will count those 3 up(total of 3, not sum of the 3).. or if not a column in this table.. another table that does the calculations so that i can uncheck Friday and then for January the count goes from 3 to 6 for the other 3 red boxes that appear for Tuesday and Wednesday.

Does that make sense?

[Anonymous]

Hi,

if I understand your requirement correctly you want to have a sum of all occurences > 5, accumulated per every occurence. I've attached a new qvw. If this doesn't meet your requirements please post expected results for each row.

Hope this Helps.

Best regards

Stefan