Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
yanivvl0
Creator III
Creator III

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

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 :

good avg.PNG

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

bad avg.PNG

can anybody make it OK ?   thanks!!

1 Solution

Accepted Solutions
malini_qlikview
Creator II
Creator II

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

View solution in original post

5 Replies
malini_qlikview
Creator II
Creator II

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
Creator III
Creator III
Author

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 .

bad avg_2.PNG

yanivvl0
Creator III
Creator III
Author

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

bad avg_3.PNG

yanivvl0
Creator III
Creator III
Author

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