Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating R12 values

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

1 Solution

Accepted Solutions
whiteline
Master II
Master II

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)

View solution in original post

10 Replies
Gysbert_Wassenaar

something like this perhaps:

sum({1<[Expected_Decision_Date]={">=01/02/2009<=31/01/2010"} >}[Weighted amount in SEK])


talk is cheap, supply exceeds demand
Not applicable
Author

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

2012-11-01_18-26-21.jpg

teempi
Partner - Creator II
Partner - Creator II

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

whiteline
Master II
Master II

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)

Not applicable
Author

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

Not applicable
Author

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?

2012-11-03_11-03-08.jpg

Regards

Thor

whiteline
Master II
Master II

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;

Not applicable
Author

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


 

whiteline
Master II
Master II

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.