topic Set Analysis Expression in QlikView

Set Analysis Expression

dineshm030 — Sat, 16 Nov 2024 00:41:47 GMT

Hi All,

I want to show the Monthwise Right Frequency in the line chart. I am using below expression.

Expression:

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

Note:

{<Month,Calendar_Month = {">=$(=date(YearStart(date#(max(Calendar_Month),'MM')),'MM'))<=$(=max(Calendar_Month))"}

Kindly help me how to show monthwise for this report.

Re: Set Analysis Expression

sunny_talwar — Tue, 12 May 2020 18:58:54 GMT

Do you have a sample where we can see this?

Re: Set Analysis Expression

dineshm030 — Wed, 13 May 2020 10:46:23 GMT

Before i apply YTD it is showing correct value.

After i Use this Month,Calendar_Month = {">=$(=date(YearStart(date#(max(Calendar_Month),'MM')),'MM'))<=$(=max(Calendar_Month))"} expression, it is showing as wrong.

=Num(Sum({<Month,Calendar_Month = {">=$(=date(YearStart(date#(max(Calendar_Month),'MM')),'MM'))<=$(=max(Calendar_Month))"}>}Aggr(if(((Count({<DCRIS_ACTIVE = {'Y'},DCRActive = {'Field Work','Meeting'},Month,Calendar_Month = {">=$(=date(YearStart(date#(max(Calendar_Month),'MM')),'MM'))<=$(=max(Calendar_Month))"}>}DISTINCT DCR_DT&DCRVentasysCustomerId) - Count({<DCRIS_CUST_TYPE={'Retailer'},DCRVISITS_PER_MONTH ={'0'},DCRActive = {'Field Work','Meeting'},DCRIS_ACTIVE = {'Y'},Month,Calendar_Month = {">=$(=date(YearStart(date#(max(Calendar_Month),'MM')),'MM'))<=$(=max(Calendar_Month))"}>}DISTINCT DCR_DT&DCRVentasysCustomerId)-Count({<DCRIS_ACTIVE = {'Y'},DCRActive = {'Field Work','Meeting'},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'},Month,Calendar_Month = {">=$(=date(YearStart(date#(max(Calendar_Month),'MM')),'MM'))<=$(=max(Calendar_Month))"}>}DCRVISITS_PER_MONTH),1,0),DCRVentasysCustomerId)) /
(Count({<IS_ACTIVE = {'Y'},Month,Calendar_Month = {">=$(=date(YearStart(date#(max(Calendar_Month),'MM')),'MM'))<=$(=max(Calendar_Month))"}>}DISTINCT VentasysCustomerId) - Count({<CUST_TYPE={'Retailer'},VISITS_PER_MTH ={'0'},IS_ACTIVE = {'Y'},Month,Calendar_Month = {">=$(=date(YearStart(date#(max(Calendar_Month),'MM')),'MM'))<=$(=max(Calendar_Month))"}>}DISTINCT VentasysCustomerId)-Count({<IS_ACTIVE = {'Y'},CUST_TYPE={'Taxi Tour','DMS MT','DMS Rtl'},Month,Calendar_Month = {">=$(=date(YearStart(date#(max(Calendar_Month),'MM')),'MM'))<=$(=max(Calendar_Month))"}>}DISTINCT VentasysCustomerId)),'###.#%')