Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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 |
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
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?
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)
Hi Sunny,
Are you using sum(UnitsSolds)? Or any other expression.
I am getting 626.
Thanks,
Sarif
Was using another expression ... but now I understand what you were looking for