Transformation: Date range split?

dszl136154 · November 19, 2012 11:25AM

HI,
I was wondering if Omniscope could solve the following problem?

I have the following table:

Start Date End Date Spend
20-Sep-12 27-Sep-12 £4,200
28-Sep-12 01-Oct-12 £2,000

I would like to draw one bar for each day between start and end dates sized by an average spend per day. I reckon to do this, I would need to convert the data into the following format:

Date Spend
20-Sep-12 £600
21-Sep-12 £600
22-Sep-12 £600
23-Sep-12 £600
24-Sep-12 £600
25-Sep-12 £600
26-Sep-12 £600
27-Sep-12 £600
28-Sep-12 £500
29-Sep-12 £500
30-Sep-12 £500
01-Oct-12 £500

Is it possible to do it in Omniscope? If yes, could you help me with it, please?
Thanks

steve · November 22, 2012 5:05AM

I would add a formula field to calculate the number of days in each period. Then another to calculate the daily spend.

Then I would merge with a sequence of all dates, using two join operators >= start and < end.

I think your £500 should be £666, assuming the end date is exclusive.

See attached.

dszl136154 · January 14, 2013 12:32PM

Hi Steve,

Thanks for that.

It works just had to twist Daily Spend formula as Start/End Dates are included.

Welcome!

Categories

Ideas Parade

Tagged