YTD Dates Issue

[engr_farhanqadr]

Dear Folks,

please guide me how can I pick YTD quantity of previous year like its 7 Nov 2014 today , so YTD of previous will be 7 Nov 2013

my table columns are below, the main date column is [date/time] and quantity column is 'quantity'. I want to show in straight table as two column QTY as current year quantity and PQTY as previous year quantity.

I have data of two year 2014 and 2013.

table 1:

LOAD 

     WeekEnd([date/time]) as weeks,

     [date/time],

     'T' & ceil(Day([date/time])/10) as Tenant,

      MonthName([date/time]) as months,

     Year([date/time]) as Year,

     date(floor([date/time])) as cdate,

     [settlement id],

     type,

     [order id],

     sku as prod_sku,

     description,

     quantity,

     marketplace,

     fulfillment,

     [order city],

     [order state],

     [order postal],

     [product sales],

     [shipping credits],

     [gift wrap credits],

     [promotional rebates],

     [sales tax collected],

     [selling fees],

     [fba fees],

     [other transaction fees],

     other,

     total

please help out

Farhan

[JonnyPoole]

You can use SET ANALYSIS in the UI expression to do this.

Another popular way to do this is to create a flag field in the data load, for example as follows:

Then when you build your chart expression (ie: Sum(Sales) )  , multiply it by the YTDFlag field... any value outside of YTD will be zero and not be included in the tally.  (ie:  sum(Sales * YTDFlag) )

table 1:

LOAD

     WeekEnd([date/time]) as weeks,

     [date/time],

     if(  Year([date/time]) = Year(Today()) , 1, 0)  as YTDFlag,

     etc....

[engr_farhanqadr]

Dear Jonathan,

I did the same but in both YTD and PYTD column values are same I want data of 2014 in YTD column and data of 2013 in PYTD column , I used sum(quantity* YTDFlag) ) in PYTD column and sum(quantity) in YTD column.

please advise.

[jduenyas]

Try this

Count({<[Date/Time] = { ">=$(MakeDate(Year(today())-1,Month(today()),Day(today())))"}  >}YourFieldToCount)

[jduenyas]

Sorry for the error in my previous post

Try this for prior year:

Count({<[Date/Time]={"=${=makedate(Year(today())-1,Month(today()),Day(today()))<makedate(year(today())-1,Month(today()),day(today())+1)" }>}YourFieldToCount)

[JonnyPoole]

The technique is sound but i don't know what you did in your load script . 

Can you share what you did in the script and also a screenshot of the chart you are using to calculate this.

[djsampat]

Hi,

View this sample YTD

YTD Calculation by Month

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

Regards

Dhruv