Sales Previous Year

[#1]

I’m searching for a rule to get the Sales (red text) of the previous Year for a selected timeline

(example: Feb 14 – Mrz15). Has anybody an idea?

Calendar
Jan 13	Feb 13	Mrz 13	Apr 13	Mai 13	Jun 13	Jul 13	Aug 13	Sep 13	Okt 13	Nov 13	Dez 13
Jan 14	Feb 14	Mrz 14	Apr 14	Mai 14	Jun 14	Jul 14	Aug 14	Sep 14	Okt 14	Nov 14	Dez 14
Jan 15	Feb 15	Mrz 15

	Sales		Sales Prev.Y.
	Feb 14		Feb 13
	Mrz 15		Mrz 14
Total	1.500.000		1.400.000

[Gysbert_Wassenaar]

Usually one would use a date field for this calculation. Suppose you have Month-Year field like in your screenshot and a Date field. You can use a set analysis expression like these:

Current year: sum({<[Month-Year]=, Date={">$(=addyears(max(Date),-2))<=$(=addyears(max(Date),-1))"}>}Sales)

Previous year: sum({<[Month-Year]=, Date={">$(=addyears(max(Date),-1))<=$(=max(Date))"}>}Sales)

[Anonymous]

Hi,

You can use a set analyses.

You must say use an expression to set:

date<addyear(max(date),-1) AND date >addyear(min(date),-1)

It should be like:

sum({$<Date = {“>$(=addyear(min(Date),-1)<$(=addyear(max(Date),-1)”}>} sales)

[#4]

Thank you, but it didn’t works. I've got only sales, if I use the variable. But I get much more sales as the selected months. This is the part of the script about the Date and the set analysis:

Script:

Date (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) As DATUM,
Year (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as JAHR,
Month (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as MONAT,
Day (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as TAG,
Date (monthstart(VPZDTT&'.'&VPZDMM&'.'&VPZDJJ), 'MMM.YYYY') as MONATJAHR,

Variable:

MinDatum =AddYears(min(MONATJAHR),-1)

MaxDatum =AddYears(max(MONATJAHR),-1)

=sum({<[MONATJAHR]=, DATUM={">$(MinDatum))<$(MaxDatum)"}>}VPUMNH)

[Gysbert_Wassenaar]

=sum({<[MONATJAHR]=, DATUM={">$(MinDatum))<$(MaxDatum)"}>}VPUMNH)

You've got an extra ) in there after MinDatum.

[#6 · Accepted solution]

Thank you very much.

I modified it a little bit and now it works.

Here the solution for all others:

Script:

Date (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) As DATUM,
Year (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as JAHR,
Month (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as MONAT,
Day (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as TAG,
Date (monthstart(VPZDTT&'.'&VPZDMM&'.'&VPZDJJ), 'MMM.YYYY') as MONATJAHR,

Variable:

MinDatum=AddYears(min(MONATJAHR),-1)

MaxDatum=AddMonths((AddYears(max(MONATJAHR),-1)),1)

=sum({<[MONATJAHR]=, DATUM={">$(MinDatum)<$(=MaxDatum)"}>}Sales)