topic Re: Calculation issue for Selected Items in QlikView

Calculation issue for Selected Items

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculation issue for Selected Items

avinashelite — Tue, 12 Jun 2018 11:16:16 GMT

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

Re: Calculation issue for Selected Items

mhmmd_srf — Tue, 12 Jun 2018 11:32:08 GMT

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

Re: Calculation issue for Selected Items

sunny_talwar — Tue, 12 Jun 2018 11:34:58 GMT

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?

Re: Calculation issue for Selected Items

sunny_talwar — Tue, 12 Jun 2018 11:44:52 GMT

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)

Re: Calculation issue for Selected Items

mhmmd_srf — Tue, 12 Jun 2018 12:29:29 GMT

Hi Sunny,

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

I am getting 626.

Thanks,

Sarif

Re: Calculation issue for Selected Items

sunny_talwar — Tue, 12 Jun 2018 12:30:22 GMT

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