Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys
I am trying to create a chart based on the table attached, where the "Weighted amount in SEK" should be calculated into R12 values.
And if I select to display 2010 to 2012, it should still calculate Feb 2009 to Jan 2010 to display in Jan 2010 as first value in the chart.
Anyone there who can help me?
I have tried searching around without any luck.
Best regards
Thor
Hi.
If the goal is just rolling amount there is a way without making changes in data model.
You could use rangesum(above(Sum(Value),0,12)).
The only issue is that with two and more dimensions (date, Manager) this expression works only in a pivot or straight table.
For charts the solution is to use explicit aggregation:
aggr(rangesum(above(Sum([Weighted amount in SEK]), 0, 12)), [Account Manager], Expected_Decision_Date)
something like this perhaps:
sum({1<[Expected_Decision_Date]={">=01/02/2009<=31/01/2010"} >}[Weighted amount in SEK])
Thanks for the reply, but it is not exactly what I am looking for.
I want to see the rolling 12 Weighted Amount value per Account Manager per Month, like in my example below where each line is the R12 value for one account manager.
Regards
Thor
Hey,
Assuming you have some sort of calendar in your data model, you could add a YearMonth field to it and then create a new calendar (let's call it RollingCalendar for now). In that calendar you'll create a field called something like RollingYearMonth and link the original YearMonths to it. So for example, the RollingYearMonth value 201206 should be linked to 12 different YearMonth values (201107, 201108, 201109... 201206).
If you then use this new RollingYearMonth as a dimension in your chart, you can calculate R12 averages, sums etc. pretty easily.
Hope this helps!
-Teemu
Hi.
If the goal is just rolling amount there is a way without making changes in data model.
You could use rangesum(above(Sum(Value),0,12)).
The only issue is that with two and more dimensions (date, Manager) this expression works only in a pivot or straight table.
For charts the solution is to use explicit aggregation:
aggr(rangesum(above(Sum([Weighted amount in SEK]), 0, 12)), [Account Manager], Expected_Decision_Date)
Excellent.
That did the job
Thanks so much. Now I can finally get rid of a 26 MB Excel sheet that did these calculations.
/Thor
Oh, and one more question.
Instead of the graph sinking when there are no transactions after (in this case 2012-05 for the blue line), it just stops. It should in fact calculate the R12 value every month up until the end of my selection which is 2012-09. Any idea why this is happening?
Regards
Thor
If you transform your chart into a table you will see.
There is no dimension values with dates more than 2012-05 for 'blue'.
It's a common question in the community.
I suggest you to add the nulls for each Date-Manager combination. It's not so hard and not so much data:
AllCombinations:
LOAD distinct
Expected_Decision_Date
Resident Data;
left join(AllCombinations)
LOAD distinct
[Account Manager]
Resident Data;
Concatenate(Data)
LOAD
*
Resident AllCombinations;
drop table AllCombinations;
Hi Whiteline,
Sorry, I am a complete nb, so I would not get this to work.
This is how my script looks. Would it still be possible to use your suggestion by renaming Expected_Decision_Date to CalendarDate and [Account Manager] to AccountManager?
Or do I need to include every field?
LOAD OpportunityName,
Status,
OF_Status,
Pipeline,
Probability,
IIP,
Unweighted,
UnweightedSEK,
Weighted,
WeightedSEK,
UnweightedOpp,
UnweightedOppSEK,
WeightedOpp,
WeightedOppSEK,
Currency,
SEK_Rate,
Date as CalendarDate,
Year,
Month,
YearMonth,
CreateDate,
EditDate,
LeadDate,
PipelineChangeDate,
StatusChangeDate,
DateBecameCustomer,
AccountManager,
Region,
Customer,
CustomerID,
CustomerType,
Channel,
InfluencedSale,
Partner,
PartnerID,
ProductFamily,
Segment,
Product,
MainProduct,
ProductType,
Process,
Industry,
SubscriptionPeriod,
Financing,
FROM
I assumed that your table with data was named 'Data'.
You have to name your table so that you could concatenate the values to it.
Just add the name (for example Data) before your load followed by semicolon.
Then just append the code that I've provided above after your load statement.
I suggest you to look through the reference manual (the script part) for better understanding of what you're doing.