Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Set Analysis Expression

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.

 

Labels (2)
2 Replies
Highlighted

Re: Set Analysis Expression

Do you have a sample where we can see this?

Highlighted
Creator II
Creator II

Re: Set Analysis Expression

Before i apply YTD it is showing correct value.

dineshm030_0-1589366747577.png

 

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

 

dineshm030_1-1589366837748.png

=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)),'###.#%')