5 Replies Latest reply on Oct 30, 2012 2:40 PM by Alex Kerin

# Can you create calculated fields by date?

So I have data pulling different retail metrics by store number into a P+L.   Right now, I am focusing on sales.  I pulled my original data from Excel so that I have a "total sales" measure, but can then split up this number into various date formats using the date dimension I have.  However, I am trying to create a dynamic P+L through the use of calculated fields and parameters so that I can see the effects of changing sales on the company as a whole.  My problem is, when I create a calculated field, I can not figure out how to just change the sales for a certain year.

I would like to just increase sales for the most recent year, but because I have a total sales number, it increases each sales number, even if I break out sales by years, months, etc.

Is there any way to create a calculated field that incorporates date?  Or do I need to pull the data from Excel differently?  Thanks!

• ###### 1. Re: Can you create calculated fields by date?

It sounds like your trying to do a what if scenario?

You could do something like

if datetrunc('year',[date])=datetrunc('year',today()) then [sales]*1.2 else [sales] end

This would increase all of this year's sales by 20%

• ###### 2. Re: Can you create calculated fields by date?

Ok, I'm still a little confused by the datetrunc formula... I don't need today's sales, but rather sales in 2008.   So would I do

if datetrunc('year',[date])=2008 then [sales]*1.2 else [sales] end  ??

Or is there a different formula I need to incorporate here instead of just putting 2008?   Thanks

• ###### 3. Re: Can you create calculated fields by date?

Almost.

you could do datetrunc('year',[date])=#1/1/2008# or datepart('year',[date])=2008

• ###### 4. Re: Can you create calculated fields by date?

Hey Alex,

so I tried this formula:

If DATEPART('year',[Date])=2008 THEN ([Total Sales])*1.2 ELSE ([Total Sales]) END

Only to get the "cannot mix aggregate and non-aggregate comparisons or results in an 'If' expression"

I do not understand this error, as the two "total sales" parts are the same.  Any thoughts?

• ###### 5. Re: Can you create calculated fields by date?

Now you are trying to compare a row level (date =2008) with what presumably is an aggregation - think column total (does [total sales equal sum([sales])??

If you have the non-aggregated sales, use this.