how to aggregate by several dimensions?

[Genevieve_M]

We have rows of data with these fields:
- item # - an item has only one task 
- item qty
- task #
- task qty
- task date 
- reject type - a task can have multiple reject types
- reject qty

I have up to 12 months' worth of these rows. 

Am trying to Task Qty subtotal by Item  for a given month.  I have the month selection determined by a selection within a pane.  

I have tried these formulas to get the Task Qty subtotal for an Item:
 - sum(distinct(Job_Quantity)) -  calculates a total that I can't reconcile to anything 
 - aggr(sum(Job_Quantity),Job_No) - produces a blank value
 - aggr(sum(Job_Quantity),distinct(Job_No)) - productes a blank value 

Can anyone please provide guidance on the correct formula?  Thanks!

[aruneshgupta]

If you have 2 dimensions (Job_No,Month) in the chart then use below expression to get subtotal and total.. 

sum(aggr(sum(distinct Job_Quantity),Job_No,Month))

Arunesh Kumar 

[Oleg_Troyansky]

Hi Genevieve,

The actual formula depends on the desired visualization. If you need to present this information in a chart (including Tables), then you don't need to use AGGR at all. 

For example, if you'd like a table with Items, Months, and the sum of Task Qty, you just need to use Item and Month (this should be a calendar field, calculated in the data load script) as Dimensions and a simple measure like this:

sum(Task_Qty)

The totals and subtotals can be requested in the Properties of the Table. Also try a Pivot table as an option.

As a side note, you NEVER want to use DISTINCT when summarizing quantities, even if someone suggested that... The only time DISTINCT is good for is if you want to count unique instances of something - for example, if you wanted to know number of unique jobs per Month, you could calculate it like this:

count(distinct Job_No)

But never with a Sum()...

Cheers,

[mikemiler]

Thanks for this interesting solution!