Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
not clear logic in your expecting result, why Deal 100 has no data for Q1?
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.
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
=
if(count(TOTAL <Deal> Amount)=1, sum(Amount),
if(max(total <Deal> aggr(sum(Amount),Deal,Quarter))=sum(Amount), sum(TOTAL <Deal> Amount) ))
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?
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