Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Genevieve_M
Contributor
Contributor

how to aggregate by several dimensions?

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!


 

Labels (1)
3 Replies
aruneshgupta
Contributor III
Contributor III

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III

Thanks for this interesting solution!