how to filter date?

[shruthibk]

Hi,

I got requirement to create 2 charts .where the dimension 'Reporting Date' in one chart it should contain the first day of every month,in another chart the same dimension 'Reporting Date' should contain all the dates.

How can i achieve it in same data model,plz help me to solve this.

thanks,

shruthi

[JonnyPoole · Accepted solution]

Do you have a field in your data model with all the dates ?  

If you do and the field is called DateField

1st chart dimension:  DateField

2nd chart dimension:  MonthStart(DateField)

Note that the 2nd chart will bucket every measure value from that month and report it under the first day of the month. Do you want that ? 

Otherwise i would suggest a monthstart() boolean flag in your data model

load

     DateField

     if( DateField=MonthStart(DateField), 1, 0 ) as MonthStartFlag

from <>

then you can restrict the rows in your 2nd chart using  MonthStartFlag = 1 in set analysis or conditional null suppression

sum( {<MonthStartFlag={0}>}  Sales) 

sum ( if (MonthStartFlag=0 , Sales) )

etc...

[shruthibk]

hi i need to show reporting date in dash board as well like in one dashboard all the reporting dates,in another dashboard only the 1st date of every month

[shruthibk]

like i need to provide the reporting date as filter

[maxgro]

for first day of the month (only the 1sr) you can use that calulated dimension

=if(Day(repdate)=1, repdate)

[shruthibk]

hi my requirement is like i need to show the filtered date in list box for ex.

Reporting date

01/01/10

02/01/10

03/01/10

it is first day of every month date

[shruthibk]

Hi thanks this is working fine and my last requirement is i need to show rolling month of 12 months data for 1st day of every month can i go with this approach

sum({<Reporting date={'<=date(monthstart(max(Reporting date))),'MMM YYYY'),>=date(addmonths(monthstart(max(Reporting date)),-12),'MMM YYYY')'>}sales)

is this the right approach?

[jyothish8807]

Hi Shruti,

Try like this:

1. For first date of month.

first create a field in script:

month(Reporting Date) as Month

Create a calculated dimension

if(Reporting Date=min(total<Month> Reporting Date),Reporting Date)

Exp:

sum(Sales)

2. For all dates:

Dimension:

Reporting Date

Exp:

sum(sales)

Hope it helps

Regards

KC

[shruthibk]

hi but this will not give rolling 12 months data right

[jyothish8807]

Hi Shruti,

For rolling 12 months, you need to modify your expression and use the same dimension if the requirement is same.

Regards

KC