Summing filtered columns

I have a repeater where I am filtering a column (ordinary cost) using a droplist, and need to set the sum of the different filters on a text widget. I also need to set the text widget to the non filtered sum for when the filter is disabled, but I am struggling so badly. Out of my depth basically. I keep asking chatgpt, but it just ends up confusing me even further.

Any help would be highly appreciated.

The rp file

Hi @sirtommy, unfortunately, I was not able to access your file, but I created a simple example that you might find helpful.

The file has a droplist, a repeater that contains three columns: Types of fruits, Subtypes, and Price, and the text label “Label_sum” that keeps the information about the current total for the column “Price”, which I store in the global variable “Sum”.

I used the “Selection changed” interaction for the droplist along with some conditions so that whenever the selection of the droplist equaled a specific value, the filter on the repeater dataset was set with the action “Add Filter”, and I also set the value of the global variable to zero by using the action “Set Variable Value”.

Then, since a repeater is redrawn whenever you modify it-- in our case, when we filter it-- I added the “Item Loaded” interaction for the repeater to be able to count the total value of the filter items along with conditions that checked what selection of the droplist was in place and actions “Set variable value” and “Set Text”, which set the value of our global variable [[Sum]] to [[Sum+Item.Price]], and text of the “Label_sum” to “Total[[Sum]]”.

I hope my file helps, and please let me know if you have any additional questions.DroplistSelection-R_countTotal.rp (62.4 KB)

1 Like

Thank you very much. I don’t know why the file was deleted, but here it is again.

I tried your method, but my text label ends up writing the syntax in stead of the calculation.

Hey @sirtommy, usually Axure displays a syntax instead of a calculation when there is something misspelled or omitted in the formula window, so you might want to check it. Anyway, I’ve finally taken a look at your file, and added some corrections, so now it works as expected. I apologize for modifying your [[Ordinaerkost]] column, I did it to see how the count works. I also added a new variable “SumFilter” to store the current sum of the filtered values. Please let me know if you have any additional questions!Project_AxureEdit.rp (1.3 MB)

Thanks so much. I think I forgot the punctuation between ‘Item’ and the item name :blush:

Is there a way I can use rich text to include currency symbol in front of the calculation?

Figured ut out! Sorry

I made everything work with the filters, but another issue is when I am not filtering at all, on a different table. I just want a text field (++TotalAdmin) to show the sum of the column below usin SumAdmin and Item.Admin, but the resulting text reads NaN in stead of the calculation. What am I doing wrong? I thought this scenario would be simpler :slight_smile:

Axure file

Hey @sirtommy, I’ve checked your file, and I noticed a few things:
First, .toLocaleString()in Axure requires a date object to be an input, and it returns a language- and region-sensitive version of the date object. Since your input is not a date, Axure is confused. You might try .SubString() or Slice() instead, but it might take some time and look cumbersome.
Then, you will need to move the actions from the “Loaded” event to the “Item Loaded” event on your repeater since you update a dataset when you filter it. You will also need set the “Wait” action between two “set variable” actions or delete the first action that sets your variable to zero in this interaction because this way your data will be distorted since Axure will take every row inside your repeater and put it through this action. Please let me know if you’re stuck or have some questions!

Hey @sirtommy, I’ve attached your file with all edits I mentioned earlier, so it could be easier to follow.

Thank you so much. I was able to do it with your instructions :slight_smile:
Btw: Chat GPT told me ToLocale could be used for separating numbers according to my local computer settings, and it does work. My dates which are formatted in format as we do it here in Norway regrettably can’t be sorted until Axure starts supporting it. But if you know a workaround for that I would be very happy!

Oh @sirtommy, are you telling that [[DateVariable.toLocaleString(“no-NO”)]] does not work for you in Axure, or do you need the date to be in a format where a month has a “zero” at the beginning, in other words, 01/01/2023 instead of 01/1/2023? Please let me know and I will take a closer look at this one.

That would be great.
As of now my date colums are formated manually the way we do it in Norway. 01.012023 is January 1st and so on, so yes that’s it. But I have not figured out how to make the repeater format it for me. And I am not sure how to use variables in this case either.

Here is the repeater with dates

Hello again. Thanks to you I have managed both summing and formatting of dates. I can test my prototype as is, but if I were to be totally happy with the result I would want for my numbers to have a space separator by the thousand. 100000->100 000
This works for my math when using toLocale (space thousand separator being in my local settings) but in my repeater rows I have tried and tried to make it work. Sometimes it works, but after adding sort it breaks again. Any ideas on how to make my repeater use this number formatting in all cases?

Hey @sirtommy, can you please attach your file or let me know what steps I should follow to recreate this behavior? This way, I’ll be able to visualize what’s happening and think about possible solutions. Also, here is a good post about dates in repeater. I’ve also created a file with dates that highlights two approaches. First, with the date.AddMonths() function, and the second is about slicing a string and manipulating numbers. I noticed a one-month difference between dates in your repeater, so you might try to use conditions with [[Item.Index]] to specify the row and Now.addMonths().toLocaleDateString(“no-NO”) to set this value for the text of the widget inside the repeater. Another way would be to set the variable’s value to a string and then slice this string, as you can see on the Page Loaded event in the attached file. I hope this helps!DateExperiments.rp (57.7 KB)

Thanks. Summing works with filtering. Sorting and formatting dates also works. The last piece in the puzzle would be for the numbers being summed (repeater columns: Admin, Ordinar, Endring and Total) to be shown with a 1000 space separator. 100000 -> 100 000 and so on. I have tried numorous ways, but this one is tricky. File here

Hey @sirtommy, thank you for sharing–it is so helpful! I’ve taken a look, and you might want to add some conditions on the “Item loaded” event of your repeater and try slicing the column’s content in the “Set text” action. I’ve attached an example that demonstrates this approach. I will keep thinking about this one in case I find an easier way to do that. Sorting and keeping spaces.rp (58.4 KB)

Thanks so much. I will look into it later. My last serious problem now is when i load two variants of the same repeater in different states of the same dynamic panel I can’t seem to get the summing variables set to zero. Here’s the file and when clicking the top tab ‘fakturaer’ it works fine both for the first dynamic panel, and also for the other states of the panel containing different varants of the faktura repeater. The different variants are triggered by the drop lists avtalenummer and regnskapsår, but only testing the first one of these is necessary to see my problem.

And the actual problem is when clicking the second top tab hendelser it loads the first panel containing a repeater fine with maths and all.
But when I change the dynamic panel below by using the droplist avtalenummer the second panel state shows the wrong calculation in the total sums.
But then when I use the ** Vis termin** droplist to filter my repeater - all math is good again. It’s just a problem loading the first state of the repeater with the right calculations which is escaping me. I have tried setting the variable values to zero everywhere I can think of without solving the issue.

Hey @sirtommy, I’ve checked your file, and it appears that the main issue is that you’re trying to store all sums of your columns in the same variables for each repeater. If you use a separate set of global variables to count the values on Item Loaded events, then you should be OK. Since “Item Loaded” events fire only once for each repeater when you load your page for the first time, using the same variable to store data from these repeaters creates some misunderstanding. I hope this helps!