Year over year comparison

[dries_clairhout]

Hello,

I want to achieve what is is in the attached image.

This is my calendar, how do I do this?

QuartersMap:
Mapping Load
RowNo() as Maand,
'Q' & Ceil(RowNo()/3) as Kwartaal
AutoGenerate (12);

Temp:
Load
Min(OrderDate) as minDate,
Max(OrderDate) as maxDate
Resident OrderHeaders;

Let varMinDate = Num(Peek('minDate', 0, Temp));
Let varMaxDate = Num(peek('maxDate', 0, Temp));
DROP Table Temp;

TempCalendar:
Load
$(varMinDate) + Iterno() - 1 as Num,
Date ($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() - 1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate as OrderDate,
week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day (TempDate) as Day,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate asc;
Drop Table TempCalendar;

Thanks in advance.

Dries

[Gysbert_Wassenaar]

That looks like a bar chart with two dimensions year and month and one measure for the bar height.

[dries_clairhout]

Hello,

That's correct,

But I wnat to achieve the following,

when I select 2018, I see 2018,2017 and 2016.

Something with Y-1 and Year -2 but I don't know how with my current calendar.

[Gysbert_Wassenaar · Accepted solution]

Ok, you could change your expression to change the user selection to include the previous two years. For example sum(Amount) would become sum({<Year={">=$(=max(Year)-2)<=$(=max(Year))"}>}Amount)

[dries_clairhout]

Hello,

Wow, i'm setting my first steps towards data set analysis.

But man, this is great.

Totally worked.

But I also have to have a table where the numbers are absolute from Y, Y-1 and Y-3.

Based on the current year.

Any ideas?

[Gysbert_Wassenaar]

What do you mean with absolute numbers? I'd think 2018, 2017 etc would be absolute numbers. Do you perhaps mean relative numbers, i.e. year 0 for this year, year -1 for last year, -2 for two years ago?

Anyway, if you have a Year field that the user makes selections in and another year-like field with relative numbers then the same kind of expression can be used with one addition. So something like sum({<Year=, RelativeYear={">=$(=max(RelativeYear)-2)<=$(=max(RelativeYear))"}>}Amount). The Year= will make the expression ignore the user selection in the Year field while the rest selects the three last RelativeYear values given the Year the user selected.

[dries_clairhout]

Hello,

I am trying to show T, Y-1 and Y-2. Y = current year and then last year and 2 years back.

This has to change with the selection.

But when I try your previous formula (in a table).

It doesnt work.

any ideas?

[Gysbert_Wassenaar]

Can you post a small demo app with example data that shows what you're trying to do?

[dries_clairhout]

Hello,

I have found it:

Sum({$<Year = {$(=max(Year)-1)}, DatumType={$(vDatumType)}>}DAML_MG*DAML_PR)

This gives me the value of the selected year -1.

thanks for all the help.

I'm setting my first steps in data analysis.

Thank you,

this is Solved