topic Re: Regarding age problem for time period based on inline in QlikView

Regarding age problem for time period based on inline

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

Hi all,

Facing problem of age for period 30 n 90 day I hv created sample data please have a look on it e r doing qliksense however based on expression e hv filtered the data range does not working properly for both period n working for day.

Re: Regarding age problem for time period based on inline

quddus_mohiuddi — Thu, 02 Aug 2018 06:37:03 GMT

Hi Abhinay,

I made some required changes in your given app. There is small tip which i want to share with you that if you are working with date range so try to use dates in Num format. So kindly check the attached app and confirm that is this your requirement.

Thanks,

Regards,

Quddus Mohiuddin

Re: Regarding age problem for time period based on inline

abhinayranjan11 — Thu, 02 Aug 2018 18:03:21 GMT

Hi Quddus Mohiuddin

Actually we have to take the max age based on max of date for each isu_id and isu id is duplicate for period of 30 day n 90 day.Can u please tell y u remove outer avg with conditions and why using nodistinct.in this expression.

Regards,

Abhinay

Re: Regarding age problem for time period based on inline

quddus_mohiuddi — Fri, 03 Aug 2018 06:18:54 GMT

Hi Abhinay,

First, If you want max age then you can use max instead of using avg function.

Second, Yes i removed outer avg set analysis conditions because as per your requirement we don't need to use the same set analysis before and after aggr() , but if we have that type of requirement in which we have different criteria before and after aggr() then we use set analysis after aggr().

Third, yes i used nodistinct because in this case, we want to aggregate complete records for age to isu_id.

Thanks,

Re: Regarding age problem for time period based on inline

abhinayranjan11 — Fri, 03 Aug 2018 06:57:43 GMT

Thanks for ur reply quddus mohiuddin

We have business date and create date from which calculating age and we are taking business date as filter in dashboard and time period for selection. We need to find average of age based on the max age on business date for each isu_id and isu id is duplicate. Please refer the excel sheet in excel sheet we r having multiple entry for business date and create date and we will get the age by difference of these two and we have to consider the max age which is on business date and rest age not to be included in calculation of expression.

Regards,

Abhinay

Re: Regarding age problem for time period based on inline

abhinayranjan11 — Tue, 07 Aug 2018 03:50:52 GMT

This is not correct answer please reply for last thread

Re: Regarding age problem for time period based on inline

raman_rastogi — Tue, 07 Aug 2018 07:38:39 GMT

Try this Expression

aggr(avg(aggr(max((Age)),ISU_ID)),ISU_ID)

Best

Raman Rastogi

Re: Regarding age problem for time period based on inline

quddus_mohiuddi — Tue, 07 Aug 2018 07:56:43 GMT

Hi Abhinay,

First of all sorry for my unavailability on time. As you mentioned your requirement in last thread, in my given sample app it is calculating Avg on age but in my second reply i have mentioned to you to use Max instead of Avg. Change in expression will be like this:

if(vselectedTimeperiod='Day',

avg(aggr(Nodistinct Max({<DateNum={'$(vSelectedDate)'},ISU_STAT-={'CLOSED'},ISU_TYPE={'P'}>}age),ISU_ID,Role)),

if(vselectedTimeperiod='90 Days',

avg(aggr(Nodistinct Max({<DateNum={'>=$(vCQED)<=$(vSelectedDate)'},ISU_STAT-={'CLOSED'},ISU_TYPE={'P'}>}age),ISU_ID,Role)),

avg(aggr(Nodistinct Max({<DateNum={'>=$(vCMED)<=$(vSelectedDate)'},ISU_STAT-={'CLOSED'},ISU_TYPE={'P'}>}age),ISU_ID,Role))))

Try this expression, this will get your required result as per your logic mentioned in last thread.

I have also attached a snapshot for reference, in which i selected one ISU_ID and some dates.

Re: Regarding age problem for time period based on inline

abhinayranjan11 — Tue, 07 Aug 2018 09:58:32 GMT

Thanks Raman But its not working. I have correct answer for day.I need for 30 day and 90 days.

Re: Regarding age problem for time period based on inline

abhinayranjan11 — Tue, 07 Aug 2018 10:39:54 GMT

Thanks for ur reply Quddus mohiuddin. Since I told I am getting correct answer for day based on inner n outer aggr in the app expression I have used.Since there is ISU_ID which is getting generated n having business date n create date and getting asssigned to assgned_USER_ID .i.e Role but their is some id which is not getting assigned on the day it is created and getting assigned to some other day.So for that day there is entry of null for role on which date it has been issued and those Id having age might getting neglected that'swhy its not giving complete set of avg for max age.I have used Firstsortedvalue(age,_Business_date) as age but this is also returning null for the entire id except which is assigned to users on first day and again the question is if I will filter by where condition len(trim(ISU_ID))>0 then this will also cause problem where they need id for displaying using distinct for each ISU_ID in some other table.