How to sum all Amount from Day 1 till today using SET ?

[paulyeo11]

Hi All

when i filter Reporting Code = 50010 , correct result is -120,107,846.65

= money(

if([Exec P&L Level] = 's','',Sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}[Amount]*-1)/1000)

, $(vMoneyFormatK_GL))

Now i get 14,031.85 K , may i know how to make it -120,107,846.65 ? when i dont select month and year.

Paul

[paulyeo11]

my QVW doc

[sunny_talwar]

Day 1 means beginning of the day? or beginning of the data?

[paulyeo11]

Hi sunny

Day 1 mean from the starting date. my data starting date is 1996 . or from the first record.

Paul

[sunny_talwar]

May be this?

=Money(If([Exec P&L Level] = 's', '', Sum({<year, month>}[Amount]*-1)), $(vMoneyFormatK_GL))

[paulyeo11]

Hi sunny

If i need to set last month stock amount :-

-1452716.83 + 38807.23 = -1413909.6

May i know how to use SET Expression to compute the above amount ?

Paul

[paulyeo11]

my QV Doc

[sunny_talwar]

Just for the most recent period? What happen to 16 Feb or 16 Jan? What values would you see for them?

[paulyeo11]

Hi Sunny

thank you for your help. Basically I need the Closing stock amount as below :-

year	month	stock movement	Closing Stock Amt
2016	march	38,807	(1,452,716.83)
2016	feb	207,237	(1,413,909.60)
2016	january	-100,105	(1,206,672.75)
2016	Dec	61,956	(1,306,777.49)
2016	Nov	-154,761	(1,244,821.00)

[paulyeo11]

enclosed my QV Doc