19 Replies Latest reply: Apr 20, 2016 8:40 PM by Sunny Talwar

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

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

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

my QVW doc

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

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

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

Hi sunny

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

Paul

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

May be this?

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

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

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

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

my QV Doc

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

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

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

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)
• ###### Re: How to sum all Amount from Day 1 till today using SET ?

enclosed my QV Doc

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

Try this:

=Money(RangeSum(

Above(RangeSum(Above(If([Exec P&L Level] = 's', '', Sum({<year, month>}[Amount]*-1)), 0, RowNo()))),

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

, \$(vMoneyFormatK_GL))

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

Hi Sunny

Look Like it does not work in my QV Ver.

As the figure look funny. Any work around ?

Paul

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

Do you have these selections?

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

i have same current selection .

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

When I open your file, I see this:

Could be a version issue, not sure

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

Hi Sunny

Yes look like i am having the ver issue , it is possible to have alternative . can i try using load script ? see does it help ? or wait let me try do in QS.

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

Ya, I think it would work in QS.

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

Something to do with different version I guess. Don't really know what is wrong Paul

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

Hi Sunny

In QV Any work around ?

• ###### Re: How to sum all Amount from Day 1 till today using SET ?

Script based solution can work, but won't be dynamic and might not changed based on selections.