My requirement is to do a line chart by day for the current quarter. All days up until today have the actual value and the target as recorded in the system. All days after today have today’s target carried forward. That value is NOT stored in the database anywhere. Worse, to get the correct target value requires a calculation.
It appears I can do one or the other. I can do the calculation
=(sum(if([site]=“special site”;[value]*.85;[value]))/1000)*2
but it won’t cast that value forward; previous() only takes it one day forward, not out to the end of the quarter.
I added a new data provide that gets JUST the last [value] by [site] and created a new variable that does manage to cast it forward:
=If([Date] >= RelativeDate(CurrentDate();-1);[fcst].[value];[current].[value])
However I have been unable to combine the two variables in a way that gets the correct result. If I try to nest the calculation inside the date check, it stops casting the data forward. The other way it fails to do the calculation and gives me the entire value. For example, when I try to do:
=((Sum(If([site]=“special site”;(If([Date] >= RelativeDate(CurrentDate();-1);[fcst].[value];[current].[value]))*0.85;(If([Date] >= RelativeDate(CurrentDate();-1);[fcst].[value];[current].[value]))))/1000)*2)
It seems to lose the check for the location and the calculation is incorrect.
Both queries contain the two key dimensions ([site] and [date])
Can anyone either:
- correct the syntax if I’m missing something obvious (not entirely unlikely)
- suggest another way to accomplish the same goal?
misanthropic777 (BOB member since 2009-05-22)