Pivot table dates range display problem

[#1]

hi folks,

i have data from 2012 jan 1-29 days.

my problem is to show the dates range as 1-7  8-15  16-23   like that

i tried calculated dimension as--   date(weekstart(salesdate),'m.dd')&'-'& date(weekend(salesdate),'m.dd')

expression: sum(sales)

problem: its showing dates range from 26-1,1-8.....23-29.

requirement: range should start from 1st day of january

may be its problem in date format , i used subfield to achieve the desire date format

where exactly the problem

plz help me out

[jagan]

Hi,

If you use WeekStart it returns the Week starting date For Jan-1, the week start is 26-1.

Try this using

date(Lunarweekstart(salesdate),'m.dd')&'-'& date(Lunarweekend(salesdate),'m.dd')

Hope this helps you

Regards,

Jagan.

[jagan]

Hi,

Use this expression instead of the above, Date Format is fixed.

=Date(Lunarweekstart('1/1/2012'),'M.DD') &'-'& date(Lunarweekend('1/1/2012'),'M.DD')

Regards,

Jagan.

[#4]

hi jagan,

thanks for ur reply

lunarweekstart solved problem , but last week range its showing 1-29  to 2-4 .

any alternative to restrict week range up to end of january

Thanks

[vijay_iitkgp]

Hi You can use:

=Date(Lunarweekstart('1/1/2012'),'M.DD') &'-'& If(date(Lunarweekend('1/1/2012'),'M.DD')<7,

date(MonthEnd('1/1/2012'),'M.DD'),date(Lunarweekend('1/1/2012'),'M.DD'))

[#6]

hi ,

if i use max date as variable  ,ie:  date(max(Date), 'DD MMM YYYY').

how can i use my variable in this expression to achieve only january 1st  to 29th

expression working on the use of transdate dimension only.

plz help me out

[jagan]

Hi,

try using the below expression

=Sum({<Transdate={'>=$(=MonthSart(vDate))<=$(=MakeDate(Year(vDate), Month(vDate), 29))'}>} Sales)

Regards,

Jagan.

[#8]

hi jagan,

here vDate=max date or min date

how i can declare vDate as Variable.

here date starting from jan 1 to 29.

thanks jagan

[CELAMBARASAN]

Hi,

     Press Ctrl+Alt+V.

     In variable overview you can add variables.

     add variable name as vDate.

     value as =Max(Date)

Celambarasan

[#10]

Hi,

Try inserting following dimension.

(TestDate need to be replaced by SalesDate / whatever dimension you have).

IF(MONTH(LunarWeekEnd(TestDate))<>MONTH(LunarWeekStart(TestDate)),NULL(),LunarWeekStart(TestDate)&'-'&DATE(LunarWeekEnd(TestDate),'DD/MM/YYYY'))

This will give you range of date before Jan end only.