Sales acc last year from selected date

[#1]

In my salesreport I have a column with the expression:

=sum({<Datum={'>=$(=date(yearstart(max(Datum)),'YYYYMMDD')) <=$(=date(max(Datum),'YYYYMMDD'))'}>}SALES)

It's sales from selected date to selected dates year start.

I would like to get a column with sales from last year from same selected date but last year. Acc LY YTD (but YTD is selected).

I also like to show the selected dates week sales per day.

One column per day, 7 columns.

[Miguel_Angel_Baeyens]

First of all I'd recommend you to create a master calendar with all your relevant sales dates. That link works, although you can find easily some other examples throughout the Community portal.

To have those 7 columns you can either set seven different expressions or use field "Day" as dimension.

YearToDate() function in that master calendar would help you to do as you want.

Hope this helps.

[#3]

Is there some examples of report with calender?

I start my script with creating a type of calender:

Set


 NoOfYearsback = 2; 













 


 


 























 









































Set


 NoOfYears = 1;
Set


 begin = makedate(Year(Today())-$(NoOfYearsback),'1','1')
;
Set


 enddate = makedate(Year(Today())+$(NoOfYears),'1','1')
;
Let


 D= $(enddate)-$(begin)+1
;
LET


 CurrDay = Num(Day(Today()), 00)
;
LET


 CurrMonth = Num(Month(Today()), 00)
;
LET


 CurrYear = Year(Today())
;
LET


 LastMonth = Num(Month(Today())-1, 00)
;
LET


 LastYear = Year(Today())-1
;

DateTable:
Load
date


($(begin) + RecNo()-1, 'YYYYMMDD') as
AllDates
Autogenerate


 ($(D))
;
ValPeriod_tmp:

Load



distinct


 (Year(AllDates) & '-' & num(Month(AllDates)) & '-01') as ValPeriod
resident



DateTable;
ValPeriod:

Load
Date


(ValPeriod, 'YYYYMMDD') as ValDatum
,
Year


(ValPeriod) as ValÅr
,
Month


(ValPeriod) as ValMånad
,

date(Addmonths(ValPeriod,-12), 'YYYYMMDD') as
Mån_12
resident



ValPeriod_tmp;
drop


 table

ValPeriod_tmp;
Load
AllDates


 as Datum
,
Year


(AllDates) as År
,

Week(AllDates) as Vecka
,

num (Month(AllDates)) as Månad
,

Day(AllDates) as Dag
,
Year


(AllDates)-1 as År_minus_ett
,
num


(Month(AllDates))-1 as Månad_minus_ett
,
Year


(AllDates) & '-' & num(Month(AllDates)) as ÅrMånad
,
Year


(AllDates) & '-' & num(Month(AllDates)) & '-' & Day(AllDates)as
ÅrMånadDag
resident


 DateTable;

I only like to select one date.