Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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...
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
like i need to provide the reporting date as filter
for first day of the month (only the 1sr) you can use that calulated dimension
=if(Day(repdate)=1, repdate)
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
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?
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
hi but this will not give rolling 12 months data right
Hi Shruti,
For rolling 12 months, you need to modify your expression and use the same dimension if the requirement is same.
Regards
KC