topic Re: Not working - Ignoring the field value in Set analysis in QlikView

Not working - Ignoring the field value in Set analysis

123qlik123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have tried to ignoring the fields in the set modifiers, but not able to ignore the field values in the set analysis in textbox.

Please find the below textbox expression

=MinString({<Field1=, Field2=>} Aggr(If($(eAverageTimeToOpenEmail) = vMinAverageTimeToOpenEmailDay, WeekDay), WeekDay))

& ' ('&

Aggr(If( Min({<Field1=, Field2=>} Aggr($(vAverageTimeToOpenEmail), WeekDay ,EmailFirstOpenTimeHour)) = vMinAverageTimeToOpenEmailbyHour, EmailFirstOpenTimeHour), EmailFirstOpenTimeHour)

& ') '&

'(' & Num(vMinAverageTimeToOpenEmailDay, '#,##0.0') & ' Days)'

Kindly find above expression variable expressions.

1. eAverageTimeToOpenEmail = Avg({<Field1=, Field2=, OpenedFlag={'Y'},SentEmailStatus={'Delivered'}, EmailActivityType={'Opened'}>} EmailOpenDuration)

2. vMinAverageTimeToOpenEmailDay = Min({<Field1=, Field2=>}Aggr($(vAverageTimeToOpenEmail), WeekDay))

3. vMinAverageTimeToOpenEmailbyHour=Min({<Field1=, Field2=>}Aggr($(vAverageTimeToOpenEmail), WeekDay ,EmailFirstOpenTimeHour))

kindly help me. Thanks a lot.!

Best Regards,

MR.

Re: Not working - Ignoring the field value in Set analysis

sunny_talwar — Mon, 20 Aug 2018 13:27:48 GMT

Try this

=MinString({<Field1=, Field2=>} Aggr(If($(eAverageTimeToOpenEmail) = vMinAverageTimeToOpenEmailDay, Only({<Field1=, Field2=>} WeekDay)), WeekDay))

& ' ('&

Only({<Field1=, Field2=>} Aggr(If( Min({<Field1=, Field2=>} Aggr($(vAverageTimeToOpenEmail), WeekDay ,EmailFirstOpenTimeHour)) = vMinAverageTimeToOpenEmailbyHour, Only({<Field1=, Field2=>} EmailFirstOpenTimeHour)), EmailFirstOpenTimeHour))

& ') '&

'(' & Num(vMinAverageTimeToOpenEmailDay, '#,##0.0') & ' Days)'

Re: Not working - Ignoring the field value in Set analysis

vamsee — Mon, 20 Aug 2018 13:28:31 GMT

Hello,

I haven't checked your set yet but I have noticed a difference in two variables.

vAverageTimeToOpenEmail

eAverageTimeToOpenEmail

I hope this is a typo in the community not in the app.

Re: Not working - Ignoring the field value in Set analysis

123qlik123 — Mon, 20 Aug 2018 13:51:08 GMT

Hi Vamsee,

Thanks for the reply.

Sorry for the typo error. Yes, both variables are same only.

Best Regards,

Re: Not working - Ignoring the field value in Set analysis

123qlik123 — Mon, 20 Aug 2018 13:54:56 GMT

Hi Sunny,

Thanks a lot for the reply.

We tried the same way but still it's not working as expected. It's working for Field1 when is select in filter, but it didn't work for all the cases of field1 or field2 selections. Like if my field1&2 values is in excluded then it's showing as null other wise it's showing the weekday.

Please suggest.

Best Regards,

Re: Not working - Ignoring the field value in Set analysis

sunny_talwar — Mon, 20 Aug 2018 13:58:40 GMT

May be a sample might help see the issue

Re: Not working - Ignoring the field value in Set analysis

jagan — Mon, 20 Aug 2018 14:15:24 GMT

Hi MR,

I think it is the problem with the MinString().

Regards,

Jagan.

Re: Not working - Ignoring the field value in Set analysis

jagan — Mon, 20 Aug 2018 14:15:59 GMT

Since you only get only one value you can use Only() it will work.

=Only({<Field1=, Field2=>} Aggr(If($(eAverageTimeToOpenEmail) = vMinAverageTimeToOpenEmailDay, WeekDay), WeekDay))

Regards,

Jagan.

Re: Not working - Ignoring the field value in Set analysis

sunny_talwar — Mon, 20 Aug 2018 14:20:51 GMT

Jagan - What would be the problem with MinString? Not sure I understand why when Only works, MinString doesn't work?