topic Re: Expression on display 3 Months data in QlikView

Expression on display 3 Months data

NavinReddy — Sun, 07 Aug 2016 10:51:56 GMT

Dear experts Good Afternoon,

Im trying to display 3 Months information on charts.i am able to display current month data.

Not able to display rest of 2 months based on selection.

i have attached sample app. please help to me get 3months information.

any expression modification.

Many Thanks,

Niru

Re: Expression on display 3 Months data

NavinReddy — Mon, 08 Aug 2016 06:59:14 GMT

Dear Experts any help!

Re: Expression on display 3 Months data

rubenmarin — Mon, 08 Aug 2016 09:49:16 GMT

Hi Niranjan, I think you only need to set the same set analisys in the first Count to avoid the filter applied by the vertical dimension, in example, for tier 2:

=count({<Month=,MonthYear={"$(=Date($(vMonth),'MMM YY'))","$(=Date($(vMonth)-30,'MMM YY'))","$(=Date($(vMonth)-60,'MMM YY'))"}>}

aggr(if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}FCT_SETUP_ID)=1

and

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-30,'MMM YY'))"}>}FCT_SETUP_ID)=1

and

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-60,'MMM YY'))"}>}FCT_SETUP_ID)<>1,

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}FCT_SETUP_ID)), FCT_SETUP_ID))

Re: Expression on display 3 Months data

NavinReddy — Mon, 08 Aug 2016 11:26:11 GMT

Hi Ruben,

Thanks you very much for reply.i am not getting actual count below chart indicate actual count.

please help me any expression mismatch.please find the sample app for your reference.Many Thanks

Best Regards,

Niru

Re: Expression on display 3 Months data

rubenmarin — Mon, 08 Aug 2016 11:54:07 GMT

Hi Niranjan, for 'Tier1- One Month', using the adpated expression I set for tier 2:

=Count({<Month=,MonthYear={"$(=Date($(vMonth),'MMM YY'))","$(=Date($(vMonth)-30,'MMM YY'))"}>}

aggr(if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}FCT_SETUP_ID)=1

and

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-30,'MMM YY'))"}>}FCT_SETUP_ID)<>1,

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}FCT_SETUP_ID)), FCT_SETUP_ID))

This are the Ids counted:

Jun 16: 5794890;5794891;5794954;5795185;5795328;5795372;5795406;5795447;5795585; 5795628;5796520;5805215;5805646

May 16: 5795330;5795407;5796200;5797308

Tell me wich ids should have been counted in this cells to check the differences.

Re: Expression on display 3 Months data

NavinReddy — Mon, 08 Aug 2016 13:15:28 GMT

Hi Rube,

here is the list of ID's for 'Tier1- One Month', when i will month Jun'16 i have to get these counts on table.

please let me know if you need more information.

Jun'16(16)= 5795328,5805646,5795372,5794954,5795406,5795407,5795185,5805215,5795447,5794890,5795628,5794891,5795330,5796520,5797308,5796200

MAY'16(14)=

5795636,5794836,5795840,5795675,5790562,5803115,5805456,5795874,5795660,5795878,5795475,5795730,5795728,5795312

Apr'16(16)=

5795385,5795418,5795452,5795301,5794914,5794939,5790566,5792423,5794937,5805457,5795443,5795870,5795661,5792396,5795811,5795820

Many Thanks,

Niranjan

Re: Expression on display 3 Months data

NavinReddy — Tue, 09 Aug 2016 05:15:46 GMT

Hi Ruben,

Good Morning,

here is the image having exact count any other way to get these count.its working only when i will select current.

previous months its not working.Thanks a lot for your help.

Best Regards,

Niranjan

Re: Expression on display 3 Months data

rubenmarin — Tue, 09 Aug 2016 06:47:19 GMT

Hi Niranjan, set analisys and $-expansions are done before the calculation of the table, so the $(vMonth) will return the same value in all columns.

One option to avoid this is setting a different expression for each column, and each column should substract 0 or 2 months from vMonth, in example, for tier 1:

=Pick(ColumnNo(),

// ===== Column1 =====

count({<Month=>}

aggr(if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date(AddMonths($(vMonth), -0),'MMM YY'))"}>}FCT_SETUP_ID)=1

and Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-30,'MMM YY'))"}>}FCT_SETUP_ID)<>1,

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date(AddMonths($(vMonth), -0),'MMM YY'))"}>}FCT_SETUP_ID)), FCT_SETUP_ID)),

// ===== Column2 =====

count({<Month=,MonthYear={"$(=Date(AddMonths($(vMonth), -1),'MMM YY'))"}>}

aggr(if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date(AddMonths($(vMonth), -1),'MMM YY'))"}>}FCT_SETUP_ID)=1

and Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-60,'MMM YY'))"}>}FCT_SETUP_ID)<>1,

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date(AddMonths($(vMonth), -1),'MMM YY'))"}>}FCT_SETUP_ID)), FCT_SETUP_ID)),

// ===== Column3 =====

count({<Month=,MonthYear={"$(=Date(AddMonths($(vMonth), -2),'MMM YY'))"}>}

aggr(if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date(AddMonths($(vMonth), -2),'MMM YY'))"}>}FCT_SETUP_ID)=1

and Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-90,'MMM YY'))"}>}FCT_SETUP_ID)<>1,

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date(AddMonths($(vMonth), -2),'MMM YY'))"}>}FCT_SETUP_ID)), FCT_SETUP_ID))

)

The AddMonths -0 is unnecesary I keep it only to show that it's the same expression for all columns but changing the set analisys for MonthYear

Re: Expression on display 3 Months data

NavinReddy — Tue, 09 Aug 2016 08:35:18 GMT

Hi Ruben,

you rocks its working fine, Thank you very much,

Best Regards,

Niranjan