Not banal duplicate values on pivot table

pljsoftware — Mon, 20 Feb 2017 16:20:14 GMT

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

topic Not banal duplicate values on pivot table in QlikView

Not banal duplicate values on pivot table