topic Re: Set analysis how do i include only records with date field greater than another in QlikView

Set analysis how do i include only records with date field greater than another

Marcushenriquesk — Fri, 18 Feb 2022 16:21:03 GMT

I have this measure to calculate the average difference of time between run date and received date:

=avg(aggr(Sum({<[PANEL_ID]-={">=30000<=39999"},[ORG_ID-REG_ORG_ID]-={"1,6"},[TEST_ID]-={">=90000<=99999"}, [RUN_DATE_TIME]={">=$([RECEIVED_DATE_TIME])"}>}RUN_DATE_TIME-RECEIVED_DATE_TIME) ,ACC_ID))

I am trying to add something to the set analysis that only includes run_dates that are greater than the received date. I am trying this:

[RUN_DATE_TIME]={">=$([RECEIVED_DATE_TIME])"}

but it doesnt seem to be working there are still negative difference being included in the output of the function.

Re: Set analysis how do i include only records with date field greater than another

Thiago_Justen_ — Sun, 20 Feb 2022 15:57:05 GMT

Hi there.

Just replace from this [RUN_DATE_TIME]={">=$([RECEIVED_DATE_TIME])"} to this [RUN_DATE_TIME]={"=[RUN_DATE_TIME]>=[RECEIVED_DATE_TIME]"}

Hope it helps you.

Re: Set analysis how do i include only records with date field greater than another

Marcushenriquesk — Sun, 20 Feb 2022 17:59:22 GMT

I attempted this but it is not working properly, not sure why, it removed the negatives but the averages are like 0.1 hours when the qa team is giving me numbers around 4-5 hours.

Re: Set analysis how do i include only records with date field greater than another

Marcushenriquesk — Sun, 20 Feb 2022 18:07:16 GMT

=avg(aggr(Sum({<[RUN_DATE_TIME]={"=[RUN_DATE_TIME]>=[RECEIVED_DATE_TIME]"}, [ORG_ID-REG_ORG_ID]-={"1,6"}>}RUN_DATE_TIME-RECEIVED_DATE_TIME), ACC_ID))

Here is what i have now. its so weird but some dates just have no data populating at all now. and also the numbers seem to be a lot lower than the predicted averages.

Re: Set analysis how do i include only records with date field greater than another

Marcushenriquesk — Sun, 20 Feb 2022 18:14:04 GMT

is it because the set analysis is on the sum level and not removing each ACC_ID that has a run date less than received date