Current vs Last Date

[tracycrown]

Dear Sir/Madam

I am trying to compare current date (Aug 26) with previous date (Aug 25) to derive the variance but I am unable to get the daa for the previous date (Aug 25) correctly, please help urgently.

Sample Data below :

Date	Item	Quantity
25/8/2016	A	264
25/8/2016	B	68
25/8/2016	C	19
25/8/2016	D	27
25/8/2016	E	25
25/8/2016	F	1
25/8/2016	G	3
25/8/2016	H	18
25/8/2016	I	14
25/8/2016	J	14
25/8/2016	K	1
25/8/2016	L	72
25/8/2016	M	3
25/8/2016	N	12
25/8/2016	O	12
25/8/2016	P	2
25/8/2016	Q	16
25/8/2016	R	4
25/8/2016	S	2
25/8/2016	T	14
25/8/2016	U	23
25/8/2016	V	3
25/8/2016	W	11
25/8/2016	X	12
25/8/2016	Y	6
25/8/2016	Z	15
26/8/2016	A	273
26/8/2016	B	72
26/8/2016	C	19
26/8/2016	D	28
26/8/2016	E	30
26/8/2016	F	1
26/8/2016	G	3
26/8/2016	H	18
26/8/2016	I	15
26/8/2016	J	15
26/8/2016	K	1
26/8/2016	L	75
26/8/2016	M	4
26/8/2016	N	12
26/8/2016	O	13
26/8/2016	P	2
26/8/2016	Q	17
26/8/2016	R	5
26/8/2016	S	2
26/8/2016	T	15
26/8/2016	U	24
26/8/2016	V	3
26/8/2016	W	12
26/8/2016	X	16
26/8/2016	Y	6
26/8/2016	Z	15
27/8/2016	A	281
27/8/2016	B	72
27/8/2016	C	19
27/8/2016	D	28
27/8/2016	E	32
27/8/2016	F	1
27/8/2016	G	3
27/8/2016	H	18
27/8/2016	I	15
27/8/2016	J	15
27/8/2016	K	1
27/8/2016	L	76
27/8/2016	M	4
27/8/2016	N	12
27/8/2016	O	13
27/8/2016	P	2
27/8/2016	Q	17
27/8/2016	R	5
27/8/2016	S	2
27/8/2016	T	16
27/8/2016	U	24
27/8/2016	V	3
27/8/2016	W	12
27/8/2016	X	16
27/8/2016	Y	6
27/8/2016	Z	15

Thank You

Tracy

[sunny_talwar · Accepted solution]

May be you need this?

Sum({<Date = {"$(=Date(Max(Date) - 1))"}>} Quantity)

[HirisH_V7]

Hi,

May be like this,

Using This expression for picking Max Date,

=sum({<Date={"$(=Date(Max(Date)))"}>} Quantity)

Similarly,For Previous Date,

=sum({<Date={"$(=Date(Max(Date)-1))"}>} Quantity)

HTH,

PFA,

Hirish

[HirisH_V7]

Hi,

Check may be other way using DayNumber,

=sum({<DayNumber={"$(=Max(DayNumber)-1)"}>} Quantity)

HTH,

PFA,

-Hirish

[effinty2112]

Hi Tracy,

                    If a script solution is acceptable then:

Data:

LOAD Date,

     Item,

     Quantity

FROM

[Test-1.xls]

(biff, embedded labels, table is Sheet1$);

Left Join(Data)

LOAD

Date+1 as Date,

Item,

Quantity as [Prev Qty]

Resident Data;

gives the day to day variance for all date >= 19/8/2016. This is for the 27th:

Date	Item	Sum(Quantity)	Sum([Prev Qty])	Variance
		708	696	12
27/8/2016	A	281	273	8
27/8/2016	B	72	72	0
27/8/2016	C	19	19	0
27/8/2016	D	28	28	0
27/8/2016	E	32	30	2
27/8/2016	F	1	1	0
27/8/2016	G	3	3	0
27/8/2016	H	18	18	0
27/8/2016	I	15	15	0
27/8/2016	J	15	15	0
27/8/2016	K	1	1	0
27/8/2016	L	76	75	1
27/8/2016	M	4	4	0
27/8/2016	N	12	12	0
27/8/2016	O	13	13	0
27/8/2016	P	2	2	0
27/8/2016	Q	17	17	0
27/8/2016	R	5	5	0
27/8/2016	S	2	2	0
27/8/2016	T	16	15	1
27/8/2016	U	24	24	0
27/8/2016	V	3	3	0
27/8/2016	W	12	12	0
27/8/2016	X	16	16	0
27/8/2016	Y	6	6	0
27/8/2016	Z	15	15	0

Good luck

Andrew

[tracycrown]

Dear Andrew

This method is something new to me and it is useful, thank you so much for your kind suggestion.

Many thanks

Tracy

[tracycrown]

Dear Hirish V

Your answer is also correct and I really appreciate your kind effort in explaining different ways of writing the script.

Many Thanks

Tracy