Expression on display 3 Months data

[NavinReddy]

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

[NavinReddy]

Dear Experts any help!

[rubenmarin]

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))

[NavinReddy]

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

[rubenmarin]

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.

[NavinReddy]

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

[NavinReddy]

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

[rubenmarin · Accepted solution]

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

[NavinReddy]

Hi Ruben,

you rocks its working fine, Thank you very much,

Best Regards,

Niranjan