Date Issues

[#1]

Hi,

  Here is my problem, i have a sales table that started last year, and i want to compare sales from this year with the year before in two ways, the first like going exactly 365 days back and the other pointing to a specific date in the past, The table goes like this:

   Date

   Sales_Amount

   Store_Id

   Equivalent_date

The last field would be the equivalent day to compare (to solve issues arround feb 29 or special day sales).

Im trying something like:

=sum({$<Equivalent_date={"=$(Date)"}, Date=>} Sales_Amount)

It just dont work

Thx in advance.

[#2]

Hi fernando,

You want to show max year and previous year comparision am i right then conver the date as a year. Can you send that app i will do your need.

Regards,

Nirmal

[khadeer]

Hi

Actual Year

sum({<Year={$(=max(Year))}>} Sales_Amount)

Last Year

sum({<Year={$(=max(Year -1))}>} Sales_Amount)

[Gysbert_Wassenaar · Accepted solution]

You could try this:

=sum({$<Equivalent_date={'$(=only(Date))'}, Date=>} Sales_Amount)

Note, if you want to use Date or Equivalent_date as dimension then set analysis won't work. You'll have to create another table:

Sales:

Load Date, Store_id, Amount, Equivalent_date,

autonumber(Store_id&Equivalent_date) as Key

Sales_LY:

Load autonumber(Store_id&Date) as Key,

Amount as Amount_LY

resident Sales;

Now you can use Date and Store_id as dimensions and as expressions sum(Amount) and sum(Amount_LY)

[makkemik]

So how can I find rolling -365 days sales with max selection of year and/or month ?