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

[yanivvl0]

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!!

[malini_qlikview · Accepted solution]

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

[sunny_talwar]

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))

[yanivvl0]

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 .

[yanivvl0]

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 ~    :

[yanivvl0]

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