| Author |
Message |
kreusch
Newbie
Joined: 04 Nov 2011
Online Status: Offline
Posts: 22
|
   Topic: Stop Running Totals using variables
Posted: 23 Aug 2016 at 8:36am |
|
Hi,
I am using variables to create running totals for a report grouped by item class for inventory. I reset the variable to 0 (w:=0), use a formula to keep a running total (w:w+ {product total}), then I display the total (numbervar w;) It works great to show me the total for each item class. (I use a different variable for the item class totals, I)
I now want to use the ending total, w, as a divisor to come up with percentages for each item class. So I take (NumberVar I/NumberVar w)*100. However it keeps using the running total for the item class group for w, rather than the total for the whole report. Is there a way to convert the total variable w, to a number and use it in the formula?
Here is what it is doing:
item class studs (I) 5000 100%
item class structural (I) 5500 52%
total: (w) 10500
Here is what I need it to do:
itme class studs (I) 5000 47%
item class structural (I) 5500 52%
total (w) 10500
I cant use summations/normal running totals because I have other variables used to evaluate the dates of the transactions
Thanks for your help
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Aug 2016 at 10:01am |
|
I don't think you use it as you have it set up now. The total value does not exist at the group level where you want to use it.
If your other criteria are row level criteria might be able to create a formula field to handle the amount per row then sum that result using group summary function which can be used in a group header.
|
|
IP Logged |
|
kreusch
Newbie
Joined: 04 Nov 2011
Online Status: Offline
Posts: 22
|
Posted: 24 Aug 2016 at 6:20am |
|
Hi Dblank,
I thought about that but am having trouble converting the totals to a number. I tried ToNumber () hoping that it would save the number but it keeps reevaluating the formula. Is there a better way to change the totals to a number so that it is saved as a value rather than a variable?
Thanks
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Aug 2016 at 6:51am |
|
In theory you can just group on the item class
create a summary as a SUM the value field at the group level
create a second summary as a SUM of the value field as a but also as a % of the same sum of the same field for the report
if the 'value' field you are summing is conditionally included or excluded use a formula field in its place
like
if field = condition then table.value else 0
|
|
IP Logged |
|
kreusch
Newbie
Joined: 04 Nov 2011
Online Status: Offline
Posts: 22
|
Posted: 26 Aug 2016 at 3:04am |
|
Hi DBlank,
Yes I would normally do the grouping and use a summation with the percentage. However I have to use variable to evaluate the dates. I use a dataset to pull in all the fiscal year data based off of the month and year the user selects. For fiscal year to date, it works fine because i just use all the data. But for the month to date i have to find the maximum date, then find the month of that date and then if that month equals the month of the invoice date I pull the total. Since I do this all with variables I am unable to sum it.
Is there a better approach that would allow me to get away from variables and then i would be able to use the summation function?
Thanks for your help
k
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 26 Aug 2016 at 4:13am |
|
if I understand you you can use a formula to create a value you could sum
//mtd formula
if {table.Datefield} in monthtodate then {table.valuefield} else 0
|
|
IP Logged |
|
|
|
