Calculation issue for Selected Items

[mhmmd_srf]

Hi All,

I have a requirement like, I have Sheet3, like below:

Now we need to capture No of Items falling under two red lines (1 in this example).

And total Items is 10.

We need to have Total Units for 1 Items / Total Units for 10 Items in sheet4.

I have some expression which is not working properly.

I am attaching test QVW file and expected result in excel with calculation.

Could anyone please help me on this.

[avinashelite]

can you explain the logic for this numbers i.e. 306 ,0 etc

CstFiscalYear	CstFiscalPeriod	[No name 1]
2018	1	306
2018	2	0
2018	3	24
2018	4	12
2018	5	12
2018	6	12
2018	7	7
2018	8	17
2018	9	12

[mhmmd_srf]

Hi Avinash,

This is sum(UnitsSolds) for  1 Item (Item No 721730) showing in Chart between two red lines with that specific selection.

Thanks,

Sarif

[sunny_talwar]

Also, can you explain how are you getting 626 here for Year 2018 and Period 1? What is Adjustable set at when you calculated the above number for units for items between red in the excel file?

[sunny_talwar · Accepted solution]

Try this

=Sum(Aggr(If(Sum(TOTAL <DIM_ITEM.ItemNumber> UnitsSold)*100/Sum(TOTAL <DIM_ITEM.ItemNumber> Aggr(Max(ForecastQuantity), CstFiscalPeriod, DIM_ITEM.ItemNumber)) >= $(v_Min)

and Sum(TOTAL <DIM_ITEM.ItemNumber>UnitsSold)*100/Sum(TOTAL <DIM_ITEM.ItemNumber> Aggr(Max(ForecastQuantity), CstFiscalPeriod, DIM_ITEM.ItemNumber)) <= $(v_Max), Sum(UnitsSold)), DIM_ITEM.ItemNumber, CstFiscalPeriod)) /

Sum(UnitsSold)

[mhmmd_srf]

Hi Sunny,

Are you using sum(UnitsSolds)? Or any other expression.

I am getting 626.

Thanks,

Sarif

[sunny_talwar]

Was using another expression ... but now I understand what you were looking for