topic Re: Set Analysis Filter in QlikView

Set Analysis Filter

dineshm030 — Sat, 16 Nov 2024 00:50:27 GMT

Hi All,

I have four columns which are A, B, C and D columns. The values should not be changing while selecting the all my columns following the expression. Kindly help me please.

Expression:

This is my expression.

Sum({<A,B,C,D>} Aggr(if((Count({<A,B,C,D>}DISTINCT DATE&CustomerId) >= VISITS_PER_MTH) and (VISITS_PER_MTH <>0),1,0),CustomerId))

Thanks in advance.

Re: Set Analysis Filter

sunny_talwar — Wed, 15 Apr 2020 20:06:17 GMT

Try this

Sum({<A, B, C, D>} Aggr(If(Count({<A, B, C, D>}DISTINCT DATE&CustomerId) >= Only({<A, B, C, D>} VISITS_PER_MTH) and Only({<A, B, C, D>} VISITS_PER_MTH) <> 0, 1, 0), CustomerId))

Re: Set Analysis Filter

Brett_Bleess — Thu, 14 May 2020 14:11:08 GMT

If Sunny's post was what you needed, we would appreciate it if you could return to your thread and use the Accept as Solution button on his post to close out the thread. If you did something different, consider posting that and then use the button to mark that post as the solution.

The only other thing I have is the following Design Blog post that may be of some help:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Regards,
Brett

Re: Set Analysis Filter

dineshm030 — Thu, 09 Jul 2020 12:07:55 GMT

Hi Sunny,

I need your help.

Num(Sum(Aggr(if(((Count({<DCRIS_ACTIVE = {'Y'},DCRActive = {'Field Work'}>}DISTINCT DCR_DT&DCRVentasysCustomerId) -
Count({<DCRIS_CUST_TYPE={'Retailer'},DCRVISITS_PER_MONTH ={'0'},DCRActive = {'Field Work'},DCRIS_ACTIVE = {'Y'}>}DISTINCT DCR_DT&DCRVentasysCustomerId)-
Count({<DCRIS_ACTIVE = {'Y'},DCRActive = {'Field Work'},DCRIS_CUST_TYPE ={'Taxi Tour','DMS MT','DMS Rtl'}>}DISTINCT DCR_DT&DCRVentasysCustomerId))) >= Only({<DCRIS_CUST_TYPE ={'*'}-{'Taxi Tour','DMS MT','DMS Rtl'},DCRVISITS_PER_MONTH={'*'}-{'0'}>}DCRVISITS_PER_MONTH),1,0),DCRVentasysCustomerId)) /
Num(Count({<IS_ACTIVE = {'Y'},VISITS_PER_MTH -={'0'}>}DISTINCT VentasysCustomerId) ,'#,##0'),'###.#%')

I am using above expression in line chart. I want to show jan to jun (YTD) data. This value is showing 68.5% for current month.

Thanks in advance.