topic Re: Sales Last Year Same Month Day in App Development

Sales Last Year Same Month Day

madnanansari — Tue, 26 Dec 2017 13:57:04 GMT

I am working Sales table have 3 years data with the below attributes:

[Date]

[Sales Amount]

The requirement is to show a TABLE where for each [Date], Sales for Last year same Month day should also come.

For example: Against the date 10-Dec-2017, the last year same Month day sales also come which is sales for 11-Dec-2016. (Sunday this month same as sunday last year same Month which is 11th december).

If I subtract 364 from [Date]; it will give me Last Year Same day.

Re: Sales Last Year Same Month Day

antonaks — Tue, 26 Dec 2017 14:13:47 GMT

Hi!

Try to use makedate()

makedate(current_year-1, month_of_current_year, day_of_current_year) as ....

Re: Sales Last Year Same Month Day

madnanansari — Tue, 26 Dec 2017 14:33:04 GMT

what to do next then?

Re: Sales Last Year Same Month Day

madnanansari — Tue, 26 Dec 2017 14:38:54 GMT

How to use set analysis to bring the sales for last year same month day?

Re: Sales Last Year Same Month Day

antonaks — Tue, 26 Dec 2017 14:47:47 GMT

Can you show how must final result seen? (Table or Diagram) May be I don't understand your question.

Re: Sales Last Year Same Month Day

sunny_talwar — Tue, 26 Dec 2017 14:53:56 GMT

May be use this approach

The As-Of Table

Re: Sales Last Year Same Month Day

madnanansari — Tue, 26 Dec 2017 14:55:45 GMT

Date Sales Sales LYSD

02-Dec-2016 101 0

03-Dec-2016 103 0

…..

01-DEC-2017 55 101

02-Dec-2017 100 103

Re: Sales Last Year Same Month Day

Anonymous — Tue, 26 Dec 2017 15:01:53 GMT

Note :

you have to get "Year" and "WEEK", out of your "Date" field

Current/selected year

Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} Sales)

Prior year (to same week number as current/selected year):

Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year)-1)}>} Sales)

Re: Sales Last Year Same Month Day

antonaks — Tue, 26 Dec 2017 15:07:18 GMT

temp_data:

LOAD Date,

Year(Date) as Year,

Month(Date) as Month,

Day(Date) as Day,

num(Month(Date)) & '-' & Day(Date) as Month_Day,

Sales

FROM

(ooxml, embedded labels, table is Лист1);

Re: Sales Last Year Same Month Day

antonaks — Tue, 26 Dec 2017 15:10:48 GMT

Try this.

Re: Sales Last Year Same Month Day

madnanansari — Wed, 27 Dec 2017 07:50:39 GMT

I need sales for the day which is 364 days back. I don't need the sale for same month same day number.

If let say today is 27th Dec 2017 and wednesday then I need the sales for wednesday the 28 Dec 2016 which is exactly 364 days back. I cannot compare Wednesday (27 dec 2017) with tuesdays (27 dec 2016) ; that's not correct.

Re: Sales Last Year Same Month Day

antonaks — Wed, 27 Dec 2017 09:03:08 GMT

Ok. Then you can create complex key with week number and day number.

But... I correctly understood that in the case of January 2016 and January 2017, you will compare 01/01/2017 and 03/01/2016, because this is one day of the week?

Re: Sales Last Year Same Month Day

antonaks — Wed, 27 Dec 2017 09:09:12 GMT

I apologize, at first the date was written incorrectly. Already fixed.

Re: Sales Last Year Same Month Day

antonaks — Wed, 27 Dec 2017 09:22:03 GMT

Re: Sales Last Year Same Month Day

madnanansari — Wed, 27 Dec 2017 09:22:49 GMT

Any guidance?

Re: Sales Last Year Same Month Day

antonaks — Wed, 27 Dec 2017 09:34:11 GMT

LOAD

Date,

Week(Date) & '-' & num(WeekDay(Date)) as WeekNum_WeekDay, // Key that you need

Sales

FROM

Re: Sales Last Year Same Month Day

sunny_talwar — Wed, 27 Dec 2017 09:49:39 GMT

Is the As-Of-Table approach not working? Have you tried it already?

Re: Sales Last Year Same Month Day

madnanansari — Wed, 27 Dec 2017 10:15:24 GMT

[AsOf Master Calendar]:

Load

[Date],

Date([Date] - 364,'D/M/YYYY') as [Date LYSMSD],

-364 as [Date LYSMSD Diff]

Resident [Master Calendar];

I am using the standard master calendar.

Now when I try: sum({$<[Date]= {"$(=Max([Date LYSMSD]))"}>} [Sales]), its not bringing the results.

Re: Sales Last Year Same Month Day

sunny_talwar — Wed, 27 Dec 2017 12:12:24 GMT

Try this

AsOfTable:

LOAD Date as AsOfDate,

Date,

'CY' as Flag

Resident [Master Calendar];

Concatenate (AsOfTable)

LOAD Date as AsOfDate,

Date([Date] - 364,'D/M/YYYY') as Date,

'PY' as Flag

Resident [Master Calendar];

Now you need to use AsOfDate in your chart instead of using Date as a dimension and this expression

Sum({<Flag = {'PY'}>}Sales)