Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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)
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)
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)
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)
=sum({<[MONATJAHR]=, DATUM={">$(MinDatum))<$(MaxDatum)"}>}VPUMNH)
You've got an extra ) in there after MinDatum.
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)