Need Help?

[paulwalker]

Hi Community,

Table1:

ID	Date	Sales
100	9/1/2014	2000
101	9/2/2014	100
102	9/7/2014	120
103	9/4/2014	325
104	9/5/2014	350
105	9/6/2014	415
106	9/17/2014	9000
107	9/18/2014	12500
108	9/9/2014	8500
109	9/1/2013	5500
110	9/2/2013	3500
111	9/7/2013	7500
112	9/4/2013	8600
113	9/5/2013	2590
114	9/6/2013	6530
115	9/17/2013	2980
116	9/18/2013	2450
117	9/9/2013	450

Fiscal year dataa.....

Here how to calculate FTD, MTD, and YTD

FTD --> Today sales (suppose 9/18/2014)

MTD---> Current month (Month start to today date 1 to 18)

YTD---> current year..(April to this Today date)

By default i want to show current year data.........

[rubenmarin]

FTD --> Sum({<Date={'$(=Today())'} Sales)

     or Sum({<Date={'$(=Max(Date))'} Sales)

MTD---> Sum({<Date={'>=$(=MonthStart(Today()))'} Sales)

     or Sum({<Date={'>=$(=MonthStart(Max(Date)))'} Sales)

YTD--->Sum({<Date={'>=$(=YearStart(Today(), 0, 4))'} Sales)

     or Sum({<Date={'>=$(=YearStart(Max(Date), 0 , 4))'} Sales)

Is this what you want?

[paulwalker]

Hi Marin,

Is it possible in One Bucket at script level..

[rubenmarin]

Not sure to understand this 'One Bucket' requirement.

Usually buckets distribute records, one record goes to one bucket, in this case 'today' sales will be in FTD, MTD and YTD, not in only one bucket.

you can create flags at script level:

If(Date=Today(), 1) as FTD,

If(Date>=MonthStart(Today()), 1) as MTD,

If(Date>=YearStart(Today(), 4), 1) as YTD

Then the expression should be something like:

Sum({1<FTD={1}>} Sales)

Sum({1<MTD={1}>} Sales)

Sum({1<YTD={1}>} Sales)

[paulwalker]

Hi Marin,

Actually i have data based on this bucket(CTD, MTD and YTD) 

i have 3 different type of buckets based Amount, Cost and UnitPrice..

Here above expressions it showing only current data only..

i want if i am click any year i want to show FTD, MTD and YTD

[paulwalker]

Hi All,

PFA,

Here i am using Today(), means --> only current year data..

It's showing 2014 data only..

when i am click 2013 year not reflect to my data.. because of using Today()

Script level

LOAD *, If(Date=Max(Date),  'FTD',

                      If(Date>=MonthStart(Max(Date)), 'MTD',

                            If(Date>=YearStart(Max(Date),0,4), 'YTD'))) as Bucket;

It showing error in Script side..

I want to show data Year wise...(FTD, MTD and YTD)

When i am click 2014 ---> 2014 data

2013---> 2013 data

[paulwalker]

please anyone can help me

[rubenmarin]

Hi Paul,

If I understand it well, i think that's not the correct approach, ie. today is 22/09/2014, today sales should sum in FTD, MTD and YTD, making this with the 'if' clauses will cause today sales to only count for FTD, not MTD or YTD.

I think you should use set analisys, working on expressions like:

FTD --> Sum({<Date={'$(=MakeDate(Max(YearField), Month(Today()), Day(Today()))'} Sales)

I don't know for past years what info you want, if the select 2013, what MTD will be shown? Sales of dic-2013? sales of sep-2013, sales from 01/09/2013 to 22/09/2013?

[paulwalker]

Hi Marin,

that's ok, but how to write in script level....