topic Re: Sales Previous Year in QlikView

Sales Previous Year

Wed, 11 Mar 2015 09:27:39 GMT

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

Re: Sales Previous Year

Gysbert_Wassenaar — Wed, 11 Mar 2015 09:34:24 GMT

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)

Re: Sales Previous Year

Anonymous — Wed, 11 Mar 2015 09:36:48 GMT

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)

Re: Sales Previous Year

Wed, 11 Mar 2015 12:32:45 GMT

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)

Re: Sales Previous Year

Gysbert_Wassenaar — Wed, 11 Mar 2015 12:38:58 GMT

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

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

Re: Sales Previous Year

Wed, 11 Mar 2015 13:26:06 GMT

Thank you very much.

I modified it a little bit and now it works.

Here the solution for all others:

Script:

Variable:

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

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

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