i'm looking for a little help using a pivot table in QV 11. i have 4 dimensions (type1, type2, name, and month). the month dimension is horizontal, the rest vertical. i currently have an expression to show "1" if the combination appears in the load tables
if(Count(distincttype1&type2&name)>0,1,0)
this works fine, but i would like to sum across all months and i'm finding it more difficult than i thought it would be. i want to keep the structure of the table as it is now and the months are automatically added as we enter a new one, so [May] + [April] + ..... is out of the question. the total column can be either a calculated dimension or an expression. i have tried aggr, sum rowno(total) and i thought just about everything else (although there is a good chance i was not doing them correctly). can somebody help me out? thanks.
Thanks Jason. I actually got it figured out. using the below expression i am able to get my count by month (1 or 0) and the total column actually sums across. i thought i had tried every combination of sum aggr possible.
in a follow up, this gave me what i was looking for, but is there a way to reference the "partial sum" column in an expression or conditional format? so this expression now gives me 1 or 0 for each month horizontally, then a total at the end. so if a "type1&type2&name" combo appears in each month from Jan-May, the total is 5. i would like to set a conditional format to use that total. so if the total is 1, it means it is the first time this "type1&type2&name" is appearing, and i would like to highlight the cell (or change the expression to include if.... then "NEW")
OK, good luck. If you can find the time to either build a dummy example or scramble the existing data you will find it's much easier for people to help you.
