Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
how to model the cloud or write expressions to get the correct value of the FCS column while maintaining the value of the ACT column?
My model is
Selections:
ForecastDate: 03/11/2016
ForecastTime: 16:42:29
Area: A
Expressions:
FCS = Num(Sum({<[Status]={'C'}, Type={'fcs'}>} Value), '#.##0')
ACT = Num(Sum({<Type={'act'}>} Value), '#.##0')
In pivot table I have
ForecastDate | ForecastTime | FlowDate | Area | FCS | ACT |
03/11/2016 | 16:42:29 | 03/11/2016 | A | 143.505.854 | 144.937.644 |
04/11/2016 | A | 294.977.856 | 147.000.764 | ||
05/11/2016 | A | 579.281.655 | 151.005.365 | ||
06/11/2016 | A | 524.726.055 | 138.741.212 | ||
07/11/2016 | A | 570.526.701 | 143.750.072 |
But the correct values in FCS column are
ForecastDate | ForecastTime | FlowDate | Area | FCS | ACT |
03/11/2016 | 16:42:29 | 03/11/2016 | A | 143.505.854 | 144.937.644 |
04/11/2016 | A | 146.975.536 | 147.000.764 | ||
05/11/2016 | A | 146.042.673 | 151.005.365 | ||
06/11/2016 | A | 132.455.625 | 138.741.212 | ||
07/11/2016 | A | 142.034.967 | 143.750.072 |
The problem is that for days 4, 5, 6, 7 I have more sessions with ForecastDate and ForecastTime.
I modeled my cloud with two tables Forecast, Data, because I need to have for the same FlowDate the ACT values. If I do a model with a single Data table where have ForecastDate and ForecastTime too I obtain always value 0 for ACT column.
For ACT data I have a value for single FlowDate, FlowHour, ClientID, Area.
A possible solution is to complete data for each row where Type = ACT with SessionID, ForecastDate, ForecastTime. This solution isn't the best beacuse my application contains more of 130.000.000 rows and this increase a lot the rows.
Any ideas?
In SourceData.xlsx a sheet Correct Result.
Thanks in advance
Luca Jonathan Paneta