topic Re: Sum aggr with conditions in QlikView

Sum aggr with conditions

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

Hello everyone,

I’m new in the community and I'm seeking your help because I can't find a solution (also performant) to my problem.

I'm trying to build an evolution line chart (dimension Month), that sums the stock quantity of each SKU, only if certain conditions are true.

I tried several ways to force the "loop" (using aggr) by SKU in order to collect and classify each quantity but it seems that when I select a group of materials the indicator does not work.

The conditions are the following:

1. If SKU stock Duration < 12 months, then SKU qty is "Good"
2. If SKU stock Duration > 12 Months, then SKU qty is "Bad".
3. If SKU stock Duration > 12 Months, but has reception movements, then SKU qty is "Good".
Else Dead.

The behavior should be like this:

1st approach not performant and only Works for one single SKU:

IF((sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(sum({$<Doc.Source={'Pos'},Time.YearMonth={"$(=MonthName(Max(%Time.Evo.YearMonth)))"}>}#StockQty, %Time.Evo.Month, Material.Code))
/
(sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(
fabs(sum({$<Doc.Source={'MovR6'},MovementAggr={'Consumptions'},%Time.YearMonth={"$(=MonthName(Max(%Time.Evo.YearMonth)))"}>}MovQty/6), %Time.Evo.Month, Material.Code))))
<=12
OR

(sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(
(sum({$<$(rMovementsReset),Doc.Source={'Mov'},
MovementAggr={'Receptions'},%Time.Type={'R12'},Time.Month=, Time.Year=, %Time.Key={"<$(=floor(AddMonths(MonthEnd(Max(%Time.Evo.YearMonth),0)))) >=$(=floor(AddMonths(MonthStart(%Time.Evo.YearMonth),-6)))"}>}#MovQty))), %Time.Evo.Month, Material.Code)))
>0
,

(sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}

aggr(
sum({< $(rPositionsReset),Doc.Source={'Pos'},Time.Date=,$(tSetStockCP),Pos.EndMonth.Flag={'X'},%Time.Type={'R12'}>} #Stockqty),%Time.Evo.Month, Material.Code)))
, 0)

2nd approach only works for one single SKU

Sum(({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(

sum({<Doc.Source={'Pos'}, Material.Code={'=((sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(sum({$<Doc.Source={"Pos"},
Time.YearMonth={"$(=MonthName(Max(%Time.Evo.YearMonth)))"}>}#Qty_Base_Unit), %Time.Evo.Month, Material.Code))
/
(sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(fabs(sum({$< Doc.Source={"MovR6"},MovementAggr={"Consumptions"}, Time.YearMonth={"$(=MonthName(Max(%Time.Evo.YearMonth)))"}>}#Qty_Base_Unit)/6), %Time.Evo.Month,.Code)))))>12'},

Material.Code={'(sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(
(sum({$<Doc.Source={"Mov"},
MovementAggr={"Receptions"},Time.Month=, Time.Year=, %Time.Key={"<$(=floor(AddMonths(MonthEnd(Max(%Time.Evo.YearMonth),0)))) >=$(=floor(AddMonths(MonthStart(%Time.Evo.YearMonth),-6)))"}>}#MovQty_Base_Unit)), %Time.Evo.Month, Material.Code)))
=0'}>}
$(='#Qty_'&%Units)), %Time.Evo.Month, Material.Code ))

Sorry for my huge post but I'm trying to explain to you my problem as best as I can

Best Regards,

Luciana

Re: Sum aggr with conditions

sunny_talwar — Tue, 24 Jul 2018 15:10:08 GMT

It's good that you have provided so much information, but I think it might be easy to understand your expression without seeing them in action... would you be able to share a sample to see what you have.... Also, is this calculating the duration?

Re: Sum aggr with conditions

luciana_lima — Wed, 25 Jul 2018 11:12:06 GMT

Hello Sunny,

Thank you for your quick feedback!

Yes, it is the Duration calculation (Current Stock \ 6month avg Consumptions.

Please check the qvw attached with my test scenario so you can see my problem. It's a little bit complex ("over-complex" for sure!) due to my data model but I think you can get the idea.

Also, I have in both objects the set analysis using the "If condition" and another try with Aggr and "complex" modifiers.

Thank you!

Re: Sum aggr with conditions

sunny_talwar — Thu, 26 Jul 2018 19:58:38 GMT

Try this

Sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}Aggr(

IF(

(sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(sum({$<Doc.Source={'Pos'},

Pos.EndMonth.Flag={'X'},%Time.Type={'R12'},Time.YearMonth={"$(=MonthName(Max(%Time.Evo.YearMonth)))"},%Units={"$(=only(%Units))"}, MaterialGroupAggr={'Paper'}>}$(='#Qty_'&%Units)), %Time.Evo.Month,Material.Code))

(sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(

fabs(sum({$< Doc.Source={'MovR6'},MovementAggr={'Consumptions'},%Time.Type={'R12'},Time.YearMonth={"$(=MonthName(Max(%Time.Evo.YearMonth)))"},%Units={"$(=only(%Units))"}, MaterialGroupAggr={'Paper'}>}$(='#Qty_'&%Units))/6), %Time.Evo.Month, Material.Code))))>12

//AND

//num((sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}aggr(

//(sum({$<$(rMovementsReset),Doc.Source={'Mov'},

//MovementAggr={'Receptions'},%Time.Type={'R12'},Time.Month=, Time.Year=, MaterialGroupAggr={'Paper'}, %Time.Key={">=$(=num(MonthStart(AddMonths(Max(%Time.Evo.YearMonth),-5)))) <=$(=floor(MonthEnd(AddMonths(Max(%Time.Evo.YearMonth),0))))"}>}$(='#MovQty_'&%Units))), %Time.Evo.Month, Material.Code)))

//,'#')=0

AND

SUM(AGGR(FABS(sum({$<$(rMovementsReset),$(tSetStockCP),Doc.Source={'MovR6'},%Time.Type={'R12'}, MaterialGroupAggr={'Paper'}, MovementAggr={'Consumptions'}>}#Qty_Base_Unit)),%Time.Evo.Month, Material.Code))>0,

(sum({$<Time.Quarter=,Time.Year=,Time.Month=,Time.YearMonth=,Time.Date=>}

aggr(

sum({< $(rPositionsReset),Doc.Source={'Pos'},Time.Date=,$(tSetStockCP), MaterialGroupAggr={'Paper'}, Pos.EndMonth.Flag={'X'},%Units={"$(=only(%Units))"}, %Time.Type={'R12'}>} $(='#Qty_'&%Units)),%Time.Evo.Month, Material.Code)))

, 0)

, Material.TopReference.Code, %Time.Evo.Month))

Re: Sum aggr with conditions

luciana_lima — Sun, 29 Jul 2018 20:19:34 GMT

Thank you Sunny!

It seems to be working. I'll perform other tests and get back to you with more feedback.

Re: Sum aggr with conditions

sunny_talwar — Mon, 30 Jul 2018 17:23:55 GMT

Sounds good