Aggregation with multiple if conditions and Average

Tue, 21 Feb 2017 09:01:02 GMT

We're trying to build a simulation chart where a change in value entered in input boxes affects the chart, but based on certain conditions.

More details in the attached Excel.

The only dimension in the chart is the monthname.

=Date(MonthStart(TblContracts.Shipment_Monthname),'MMM-YY')

To calculate the margin value in the chart, we wrote a simple aggregation expression.

((Aggr(sum(TblContracts.SP*TblContracts.OutstandingQuantity), TblContracts.Shipment_Monthname))
-(Aggr(sum(TblContracts.CP*TblContracts.OutstandingQuantity), TblContracts.Shipment_Monthname)))/
(Aggr(sum(TblContracts.SP*TblContracts.OutstandingQuantity), TblContracts.Shipment_Monthname))

This expression will ensure that irrespective of the SP_Index or CP_Index the Margin value for each month is evaluated simply based on the available months.

As mentioned in the excel the entry zones will affect only those months with the index ID. I managed to get it work for 1 combination of SP_Index and CP_Index. Here's the expression:

If(MonthName(vminShipmentDate)=TblContracts.Shipment_Monthname,
(Avg((Aggr(sum(If(MonthName(vminShipmentDate)=TblContracts.Shipment_Monthname and TblContracts.CP_Index='A' and TblContracts.SP_Index='B',(TblContracts.SP+B1)*TblContracts.OutstandingQuantity)), TblContracts.Shipment_Monthname) - Aggr(sum(If(MonthName(vminShipmentDate)=TblContracts.Shipment_Monthname and TblContracts.CP_Index='A' and TblContracts.SP_Index='B',(TblContracts.Unit_CP+A1)*TblContracts.OutstandingQuantity)), TblContracts.Shipment_Monthname))/(Aggr(sum(If(MonthName(vminShipmentDate)=TblContracts.Shipment_Monthname and TblContracts.CP_Index='A' and TblContracts.SP_Index='B',(TblContracts.Net_Unit_SP+B1)*TblContracts.OutstandingQuantity)), TblContracts.Shipment_Monthname)))),
If(MonthName(AddMonths(vminShipmentDate,1))=TblContracts.Shipment_Monthname,
(Avg((Aggr(sum(If(MonthName(AddMonths(vminShipmentDate,1))=TblContracts.Shipment_Monthname and TblContracts.CP_Index='A' and TblContracts.SP_Index='B',(TblContracts.Net_Unit_SP+B2)*TblContracts.OutstandingQuantity)), TblContracts.Shipment_Monthname) - Aggr(sum(If(MonthName(AddMonths(vminShipmentDate,1))=TblContracts.Shipment_Monthname and TblContracts.CP_Index='A' and TblContracts.SP_Index='B',(TblContracts.Unit_CP+A2)*TblContracts.OutstandingQuantity)), TblContracts.Shipment_Monthname))/(Aggr(sum(If(MonthName(AddMonths(vminShipmentDate,1))=TblContracts.Shipment_Monthname and TblContracts.CP_Index='A' and TblContracts.SP_Index='B',(TblContracts.Net_Unit_SP+B2)*TblContracts.OutstandingQuantity)), TblContracts.Shipment_Monthname))))
))

A1,A2 & B1,B2 are variables that represent jan and feb values respectively.

I am unable to include the rest of the index variables into this expression for each month - the variables C1, C2, D1, D2,E1, E2 are to be included to give an aggregated value for each month in the chart.

Any help would be appreciated.

Let me know if you need more details.

topic Aggregation with multiple if conditions and Average in QlikView

Aggregation with multiple if conditions and Average