Distinct Count and Aggregation

[chiranjivdas]

I have the following data set

Deal	Quarter	Amount
Deal 100	Q1	20
Deal 100	Q2	200
Deal 100	Q3	50000
Deal 100	Q4	-5000
Deal 101	Q3	26000
Deal 102	Q2	47000
Deal 102	Q3	500
Deal 103	Q4	7800

I want to display it as follows

Deal	Q1	Q2	Q3	Q4
Deal 100			45220
Deal 101			26000
Deal 102		47500
Deal 103				7800
Deal Count	0	1	2	1

Basically, I want to count deal just once even though it books across multiple quarters and aggregate the amount in the quarter that has the biggest chunk of the booking.

Could you please help?

Thanks,

CD

[Anonymous]

not clear logic in your expecting result, why Deal 100 has no data for Q1?

[chiranjivdas]

Because thats how i want it to be.... a deal should appear only once... in the quarter that has the biggest dollar amount... deal 100 has the biggest dollar amount of 50000 in Q3... so it should show up in Q3 as aggregate: 20 + 200 + 50000 - 5000 = 45220.

[#4]

Here is the solution.

The key to the solution is that for each Deal, identify a Quarter that has received highest Amount.

This expression in Pivot table does the horse work.

if(min(Aggr(rank(Amount),Deal,Quarter))=1, Aggr(Sum(Total <Deal> Amount),Deal,Quarter))

The first part of the expression ranks highest amount. Each Quarter in the Deal will receive a rank which is than checked in 'if' condition whether it is minimum (=1). If the check returns true, it means we are now able to point towards correct Quarter (that holds highest amount) in the Deal.

So then we decide to calculate the Total amount.

In the chart properties/Presentation tab, it is required to uncheck 'Suppress Zero values' so that Q1 column is shown as well.

Unfortunately, I couldn't figure out how to get Deal count at the bottom of Pivot table. (though it can be done in straight table, you would have to sacrifice the cross-table design which is not possible in straight table)

See the attached application.

Warm Regards,

Kalpesh Jain

[maxgro]

=

  if(count(TOTAL <Deal> Amount)=1, sum(Amount),

  if(max(total <Deal> aggr(sum(Amount),Deal,Quarter))=sum(Amount), sum(TOTAL <Deal> Amount) ))

[chiranjivdas]

Thanks... both solutions work. But now as I looked into the data set more, I realized I need to 'cleanse' the data at the time of the load. So if a deal has dollar amounts spread over multiple quarters, I want to include a load script that will identify the first quarter that it ever booked in and aggregate the amount in that quarter. For example

Deal 100

Q1: 20

Q2: 200

Q3: 50000

Q4: -5000

I want the script load to pick up 45220 in Q1. Is there a conditional script that I can insert in the load statement to achieve this?

[#7]

Just append this to the existing load statement

Left Join(Deals)

Load

Deal, MinString(Quarter) as [Quarter],

sum(Amount) as [Total Amount]

Resident Deals

Group by Deal;

Best Regards,

Kalpesh Jain