Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 14 May 2018 07:16:25 +0200Calc: Running total based on Weekshttps://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/ I'm developing a spreadsheet that holds a list of customers that are given a weekly order at a local market.
Sheet A is the customer list with their order in columns C - F, which is then totalled in column J. Column K is a flag for orders placed on hold while a customer is unable to collect an order while away.
![image description](/upfiles/1526111141165143.png)
The order quantities rarely change so I'm wanting to total the same numbers in the same columns each week.
Sheet B shows the same customers with the accumulating total in column B, and, even though it's not shown in the example, column C will freeze the total of any order that is flagged in sheet A, column K, with a '"h".
![image description](/upfiles/15261113013848137.png)
So, I think I need to look at the date to see when a week rolls over, so the order can be recalculated to update the running total, but haven't worked that out yet.
I need to find a way of counting the seven days and calculating the (accumulating) running total.
I'm not very experienced at spreadsheets, so would appreciate someone out there showing me the light.
If anyone has the time, I would also like to know how to set monthly, quarterly and yearly totals.
Many thanks in advance.
Sat, 12 May 2018 10:08:28 +0200https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/Comment by Jim K for <p>I'm developing a spreadsheet that holds a list of customers that are given a weekly order at a local market.
Sheet A is the customer list with their order in columns C - F, which is then totalled in column J. Column K is a flag for orders placed on hold while a customer is unable to collect an order while away.</p>
<p><img alt="image description" src="/upfiles/1526111141165143.png"></p>
<p>The order quantities rarely change so I'm wanting to total the same numbers in the same columns each week.</p>
<p>Sheet B shows the same customers with the accumulating total in column B, and, even though it's not shown in the example, column C will freeze the total of any order that is flagged in sheet A, column K, with a '"h".</p>
<p><img alt="image description" src="/upfiles/15261113013848137.png"></p>
<p>So, I think I need to look at the date to see when a week rolls over, so the order can be recalculated to update the running total, but haven't worked that out yet.
I need to find a way of counting the seven days and calculating the (accumulating) running total.
I'm not very experienced at spreadsheets, so would appreciate someone out there showing me the light.</p>
<p>If anyone has the time, I would also like to know how to set monthly, quarterly and yearly totals. </p>
<p>Many thanks in advance. </p>
https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154767#post-id-154767The example is not clear. How does `$9.00` and `3` total `$13.50`? Or `$6.00` and `7` total `$6.00`?
Please edit the question to give a simplified example that demonstrates what you are asking. See [guidelines for asking](https://ask.libreoffice.org/en/question/137128/guidelines-for-asking/#138301).Sun, 13 May 2018 08:02:30 +0200https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154767#post-id-154767Comment by Lupp for <p>I'm developing a spreadsheet that holds a list of customers that are given a weekly order at a local market.
Sheet A is the customer list with their order in columns C - F, which is then totalled in column J. Column K is a flag for orders placed on hold while a customer is unable to collect an order while away.</p>
<p><img alt="image description" src="/upfiles/1526111141165143.png"></p>
<p>The order quantities rarely change so I'm wanting to total the same numbers in the same columns each week.</p>
<p>Sheet B shows the same customers with the accumulating total in column B, and, even though it's not shown in the example, column C will freeze the total of any order that is flagged in sheet A, column K, with a '"h".</p>
<p><img alt="image description" src="/upfiles/15261113013848137.png"></p>
<p>So, I think I need to look at the date to see when a week rolls over, so the order can be recalculated to update the running total, but haven't worked that out yet.
I need to find a way of counting the seven days and calculating the (accumulating) running total.
I'm not very experienced at spreadsheets, so would appreciate someone out there showing me the light.</p>
<p>If anyone has the time, I would also like to know how to set monthly, quarterly and yearly totals. </p>
<p>Many thanks in advance. </p>
https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154703#post-id-154703Quoting @Sights: "Sheet A is the customer list with their order in columns C - F, ..."
That's not what I see - and it's even nothing I would assume possible.
If not a customer is restricted to order once and never again, you need to list the orders using either a "foreign key" into a customer list or the customer name itself in this role.
Concerning the weekly totals you will run into vast complications as long as not the date is made part of each order (row of the respective sheet).Sat, 12 May 2018 11:50:49 +0200https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154703#post-id-154703Comment by Lupp for <p>I'm developing a spreadsheet that holds a list of customers that are given a weekly order at a local market.
Sheet A is the customer list with their order in columns C - F, which is then totalled in column J. Column K is a flag for orders placed on hold while a customer is unable to collect an order while away.</p>
<p><img alt="image description" src="/upfiles/1526111141165143.png"></p>
<p>The order quantities rarely change so I'm wanting to total the same numbers in the same columns each week.</p>
<p>Sheet B shows the same customers with the accumulating total in column B, and, even though it's not shown in the example, column C will freeze the total of any order that is flagged in sheet A, column K, with a '"h".</p>
<p><img alt="image description" src="/upfiles/15261113013848137.png"></p>
<p>So, I think I need to look at the date to see when a week rolls over, so the order can be recalculated to update the running total, but haven't worked that out yet.
I need to find a way of counting the seven days and calculating the (accumulating) running total.
I'm not very experienced at spreadsheets, so would appreciate someone out there showing me the light.</p>
<p>If anyone has the time, I would also like to know how to set monthly, quarterly and yearly totals. </p>
<p>Many thanks in advance. </p>
https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154705#post-id-154705Contributors from the world of real business would advise you to not use Calc for the purpose behind your question, but to create a database for it.Sat, 12 May 2018 11:54:10 +0200https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154705#post-id-154705Comment by Lupp for <p>I'm developing a spreadsheet that holds a list of customers that are given a weekly order at a local market.
Sheet A is the customer list with their order in columns C - F, which is then totalled in column J. Column K is a flag for orders placed on hold while a customer is unable to collect an order while away.</p>
<p><img alt="image description" src="/upfiles/1526111141165143.png"></p>
<p>The order quantities rarely change so I'm wanting to total the same numbers in the same columns each week.</p>
<p>Sheet B shows the same customers with the accumulating total in column B, and, even though it's not shown in the example, column C will freeze the total of any order that is flagged in sheet A, column K, with a '"h".</p>
<p><img alt="image description" src="/upfiles/15261113013848137.png"></p>
<p>So, I think I need to look at the date to see when a week rolls over, so the order can be recalculated to update the running total, but haven't worked that out yet.
I need to find a way of counting the seven days and calculating the (accumulating) running total.
I'm not very experienced at spreadsheets, so would appreciate someone out there showing me the light.</p>
<p>If anyone has the time, I would also like to know how to set monthly, quarterly and yearly totals. </p>
<p>Many thanks in advance. </p>
https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154704#post-id-154704**Please** don't append images, but real examples (.ods file in case of Calc questions).Sat, 12 May 2018 11:52:27 +0200https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154704#post-id-154704Answer by Sights for <p>I'm developing a spreadsheet that holds a list of customers that are given a weekly order at a local market.
Sheet A is the customer list with their order in columns C - F, which is then totalled in column J. Column K is a flag for orders placed on hold while a customer is unable to collect an order while away.</p>
<p><img alt="image description" src="/upfiles/1526111141165143.png"></p>
<p>The order quantities rarely change so I'm wanting to total the same numbers in the same columns each week.</p>
<p>Sheet B shows the same customers with the accumulating total in column B, and, even though it's not shown in the example, column C will freeze the total of any order that is flagged in sheet A, column K, with a '"h".</p>
<p><img alt="image description" src="/upfiles/15261113013848137.png"></p>
<p>So, I think I need to look at the date to see when a week rolls over, so the order can be recalculated to update the running total, but haven't worked that out yet.
I need to find a way of counting the seven days and calculating the (accumulating) running total.
I'm not very experienced at spreadsheets, so would appreciate someone out there showing me the light.</p>
<p>If anyone has the time, I would also like to know how to set monthly, quarterly and yearly totals. </p>
<p>Many thanks in advance. </p>
https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?answer=154786#post-id-154786Sorry Lupp, I did say I was a novice! : (
This is the first time I have posted an example of my sheets. I didn't know I could post "real examples".
Maybe I should use a database, I am much more proficient at programming in that area.
Hey Jim K, the figures in columns C,D & F, represent numbers of Avocardos. There is a formula multiplying the unit price for 1st and 2nd grade fruit, while the 3rd grades in column F are free - hence $6 and 7 total $6.
Sorry for the confusion, and maybe I will look at starting again with a database. : )
Sun, 13 May 2018 13:34:25 +0200https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?answer=154786#post-id-154786Answer by Lupp for <p>I'm developing a spreadsheet that holds a list of customers that are given a weekly order at a local market.
Sheet A is the customer list with their order in columns C - F, which is then totalled in column J. Column K is a flag for orders placed on hold while a customer is unable to collect an order while away.</p>
<p><img alt="image description" src="/upfiles/1526111141165143.png"></p>
<p>The order quantities rarely change so I'm wanting to total the same numbers in the same columns each week.</p>
<p>Sheet B shows the same customers with the accumulating total in column B, and, even though it's not shown in the example, column C will freeze the total of any order that is flagged in sheet A, column K, with a '"h".</p>
<p><img alt="image description" src="/upfiles/15261113013848137.png"></p>
<p>So, I think I need to look at the date to see when a week rolls over, so the order can be recalculated to update the running total, but haven't worked that out yet.
I need to find a way of counting the seven days and calculating the (accumulating) running total.
I'm not very experienced at spreadsheets, so would appreciate someone out there showing me the light.</p>
<p>If anyone has the time, I would also like to know how to set monthly, quarterly and yearly totals. </p>
<p>Many thanks in advance. </p>
https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?answer=154788#post-id-154788Wrongly assuming you might even be less familiar with databases (which I had to "respect" for some time, but didn't like myself), I made an example for the running subtotal regarding the hints I gave in my comment above.
The result does neither emphasize "prettysheeting" nor strict closeness to the structure shown in the images attached to the question. My concern was to exemplfy an idea how to do such things with spreadsheets without disregarding the most basic principles of databasing.
As I very rarely designed a (even extremely simple) database myself, I may not be the best advisor in this case. Since the mentioned example once was made, however, I now attach it **[here](/upfiles/1526213180146008.ods)** with due modesty. Sun, 13 May 2018 14:06:39 +0200https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?answer=154788#post-id-154788Comment by Sights for <p>Wrongly assuming you might even be less familiar with databases (which I had to "respect" for some time, but didn't like myself), I made an example for the running subtotal regarding the hints I gave in my comment above. </p>
<p>The result does neither emphasize "prettysheeting" nor strict closeness to the structure shown in the images attached to the question. My concern was to exemplfy an idea how to do such things with spreadsheets without disregarding the most basic principles of databasing. </p>
<p>As I very rarely designed a (even extremely simple) database myself, I may not be the best advisor in this case. Since the mentioned example once was made, however, I now attach it <strong><a href="/upfiles/1526213180146008.ods">here</a></strong> with due modesty. </p>
https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154853#post-id-154853Thanks very much for your effort Lupp, I do appreciate the time you put into this example.
I will have a better look at it tonight, and also the possibility of creating a database. Used to to them 25 or more years ago, when I worked in IT, but probably a little rusty by now - should be fun.Mon, 14 May 2018 07:16:25 +0200https://ask.libreoffice.org/en/question/154697/calc-running-total-based-on-weeks/?comment=154853#post-id-154853