How to remove some values from dimension field

[abc_18]

Hi,

I have one dimension field called period, and data is like

Period
2015-05
2015-06
2015-07
2015-08
2016-03
2016-04
2016-05
2016-05

need to show record only till April(2016-04).

This report is getting generated monthly manually in excel and requirement is that, current month record should not come ie May data(2016-05).

How to achieve this by using calculated dimension??

[sunny_talwar]

May be like this:

If(MonthStart(Date#(Period, 'YYYY-MM')) <= MonthEnd(Today(), -1),  Date(MonthStart(Date#(Period, 'YYYY-MM')), 'YYYY-MM'))

UPDATE: I have used excessive formatting just because I was not sure if your Period was read as Date or Text. If you know it is read as date, then you might be able to simplify to this:

If(Period <= MonthEnd(Today, -1), Period)

[PrashantSangle]

Hi,

No need to use calculated dimension.

modify your expression like

sum({<Period={"<=$(=Date(AddMonths(Today(),-1),'YYYY-MM')"}>}Sales)

Regards

[sunny_talwar]

Great idea . But I guess this will not allow for selections. May be like this to allow for selections

Sum({<Period *= {"<=$(=Date(AddMonths(Today(),-1),'YYYY-MM')"}>}Sales)

[abc_18]

Hi Sunny,

In my report after applying this in calculated dimension

If(Period <= MonthEnd(Today, -1), Period) only that column is coming as blank, but requirements is like entire row record should not appear.

3rd and 4th row data should not come in report.

Vendor Name	Cost Center	CCStatus	T1 Code	T2 Code	Account Code	Month	Period	Amount
AUS	148	ACTIVE		148	62041025	2015-10	2016-01	372
AUS	148	ACTIVE		148	62041025	2015-11	2016-02	5778
AUS	148	ACTIVE		148	62041025	2015-12	2016-06	3376
AUS	148	ACTIVE		148	62041021	2015-10	2016-05	435375

Here in my report

Date(MonthStart(worked_date,3),'YYYY-MM') is as Period,

and worked date is date field like 01/02/2016

[sunny_talwar · Accepted solution]

You need to check 'Suppress When Value Is Null' on the dimension tab for this particular dimension. Try that and I believe it would fix your issue

[Anonymous]

if you want display the data current month-1 so that

in edit script at dimension if(period<>'(max(date))-1'as period

try this one

[abc_18]

Hi Sunny,

Ya it's working now..Thanks a lot!!