topic Re: Complicate avarage for not Similar number of steps in different entities. in QlikView

Complicate avarage for not Similar number of steps in different entities.

yanivvl0 — Mon, 22 May 2017 06:46:16 GMT

hi to all ,

i have an entity called "BakashaID" in it there can be only 4 steps (step 4 is final with no duration) called "Shalav_Sug_Shalav_code" ( uniq key step in entity called "Gk_IdShlav" ) and the dates for each step. i need to calc the avg of months for each step , i have an expression that does that :

=sum( {<Subject={1},Gk_IdShlav = {"=Aggr(Max(TOTAL <BakashaID> Shalav_Sug_Shalav_code), BakashaID, Shalav_Sug_Shalav_code) = 4 or Aggr(Max(TOTAL <BakashaID> Shalav_Sug_Shalav_code), BakashaID, Shalav_Sug_Shalav_code) > Shalav_Sug_Shalav_code"}>} aggr ( avg ( Interval( to_date - from_date ,'d') / 30 ) , Shalav_Sug_Shalav_code ) )

its work fine when all entities have the same number of steps :

But when not all entities have the same number of steps its not working good :

can anybody make it OK ? thanks!!

Re: Complicate avarage for not Similar number of steps in different entities.

malini_qlikview — Mon, 22 May 2017 11:42:34 GMT

Hi,

Instead of using such a big calculated expression, i would suggest you can try the below,

1. Create a field to calculate the difference of month between from_date and To_Date in the script

eg: (To_date-From_date)/30 As MonthDiff

2. Create a bar chart with "Shalav_Sug_Shalav_code" (dimension) and a expression - Round(Avg(MonthDiff))

This will work either you have all entites or less entities.

Attached is the sample app with working chart. Hope it helps

Re: Complicate avarage for not Similar number of steps in different entities.

sunny_talwar — Mon, 22 May 2017 11:46:18 GMT

May be you need to add BakashaID as a Aggr() function dimension

=Sum({<Subject={1}, Gk_IdShlav = {"=Aggr(Max(TOTAL <BakashaID> Shalav_Sug_Shalav_code), BakashaID, Shalav_Sug_Shalav_code) = 4 or Aggr(Max(TOTAL <BakashaID> Shalav_Sug_Shalav_code), BakashaID, Shalav_Sug_Shalav_code)> Shalav_Sug_Shalav_code"}>} Aggr (Avg (Interval(to_date - from_date, 'd')/30), Shalav_Sug_Shalav_code, BakashaID))

Re: Complicate avarage for not Similar number of steps in different entities.

yanivvl0 — Tue, 23 May 2017 06:02:43 GMT

hi Sunny , i tried but something goes wrong ,

for step 1 it calculate the sum of all months , for step 2 the value is too low , and step 3 is missing...

i attached data so any one can check it out - Thanks .

Re: Complicate avarage for not Similar number of steps in different entities.

yanivvl0 — Tue, 23 May 2017 06:22:38 GMT

hi Malini , great idea ,

it make the sum OK , i tried to calc the avg , but there is an error , only step 3 is good because it single ,

step 1 need to be something like 9.8 ~ :

Re: Complicate avarage for not Similar number of steps in different entities.

yanivvl0 — Tue, 23 May 2017 07:43:22 GMT

hi Malini - sorry - its simple and amazing solutuin , THANKS !!!