last 2 months using where clause

[DipeshVadgama]

Hi,

I am trying to do calculation in pivot to show only last 2 month data.

My Dimension is Month and

I need Expressions as Count of Distinct (ID) data only When

1. [Actual End Date] <= [Gate Schedule End Date]

2. Month of  [Actual End Date] should be last 2 month ( Eg Sep & Oct )

3. Country = 'India'

4. PASS/FAIL = 'PASS'

[Anil_Babu_Samineni]

Perhaps this, Haven't test.

Count({<[Actual End Date] = {"=Only({<Month = {">=$(=AddMonths(Max(Month), -1) <=$(=Max(Month)))"}>} [Actual End Date]) <= [Gate Schedule End Date]"}, Country = {"India"}, [PASS/FAIL] = {'PASS'}>} DISTINCT Data)

[DipeshVadgama]

Thanks, But It does not works

[prat1507]

Please find attached the required app.

Regards

Pratyush

[DipeshVadgama]

Thank Pratyush,

Request you to please send me syntax, I can not open file.

[prat1507 · Accepted solution]

I've used this as my dimension and suppressed the value NULL

=Upper(If(Date([Actual End Date])>=MonthStart(AddMonths(Today(),-2)) and Date([Actual End Date])<MonthStart(AddMonths(Today(),0)) and [Actual End Date]<[Gate Scheduled End Date],

Date([Actual End Date],'MMM-YY')))

and in the expression I'm using ;

=Count({<Country={'India'},[PASS/FAIL]={'PASS'}>}ID)

[DipeshVadgama]

Thanks buddy it helped me.

[prat1507]

Glad to help

[sunny_talwar]

Using prat1507‌'s sample attached, I propose another solutions

Dimension

DTM

Expression

=Count({<Country={'India'},[PASS/FAIL]={'PASS'}, DTM = {">=$(=Date(MonthStart(Today(), -2), 'MMM-YY'))<=$(=Date(MonthStart(Today()), 'MMM-YY'))"}>} If([Actual End Date]<=[Gate Scheduled End Date], ID))

Here I modified the DTM that Pratyush created like this

Tab:

LOAD ID,

     [PASS/FAIL],

     [Gate Actual Start Date],

     [Gate Scheduled Start Date],

     [Gate Scheduled End Date],

     [Actual End Date],

     [Project Scheduled Start Date],

     [Project Actual Start Date],

     [Project Actual End Date],

     [Project Scheduled End Date],

     Country,

     Date(MonthStart([Actual End Date]),'MMM-YY') as DTM,

     If([Actual End Date]<=[Gate Scheduled End Date], 1, 0) as Flag

FROM

[Test File.xls]

(biff, embedded labels, table is Sheet1$);

I also created a flag, but was not sure if these two dates come from the same file or not... if they are, then creating this flag might come in very handy as you will be able to then use this expression

=Count({<Country={'India'},[PASS/FAIL]={'PASS'}, DTM = {">=$(=Date(MonthStart(Today(), -2), 'MMM-YY'))<=$(=Date(MonthStart(Today()), 'MMM-YY'))"}, Flag = {1}>} ID)

I personally prefer to use set analysis over calculated dimensions, but there are times when the calculations dimension come in handy.

Best,

Sunny