Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

19 Replies
sunny_talwar

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))

Capture.PNG

paulyeo11
Master
Master
Author

Hi Sunny

Look Like it does not work in my QV Ver.

As the figure look funny. Any work around ?

figure look funny.png

Paul

sunny_talwar

Do you have these selections?

Capture.PNG

sunny_talwar

When I open your file, I see this:

Capture.PNG

Could be a version issue, not sure

paulyeo11
Master
Master
Author

i have same current selection .

current selection.png

sunny_talwar

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

paulyeo11
Master
Master
Author

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.

sunny_talwar

Ya, I think it would work in QS.

paulyeo11
Master
Master
Author

Hi Sunny

In QV Any work around ?

in QS.png

sunny_talwar

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