Running Total by Category & Date

How can i calculate running total by category sorted by Date.
I know there is option available by using running_total(Amount) from calculation but i need to show by each category and date. Amount should be reset by change of category.

cat | amount | Date | RunningTotal
1 | 10 | Day1 | 10
1 | 10 | Day2 | 20
1 | 10 | Day3 | 30
2 | 10 | Day1 | 10
2 | 10 | Day2 | 20
2 | 10 | Day3 | 30
2 | 10 | Day4 | 40
3 | 10 | Day1 | 10

1 16 8,778
16 REPLIES 16

Hi @adeel_nazir,

If I understood correct, Are you looking for result like

Cat | RunningTotal
1| 60
2|100
3|10

If yes then , if you declare runningtotal as measure in your view file then looker will take care of it.

e.g.

measure: runningTotal{
type: sum
sql: ${TABLE}.“runningTotal” ;;
}

what would be the result if i add date column as well?

This is not running total this will only calculate the sum for each category

From your above example can you please explain the output you are expecting for clear understanding. 🙂

Here is example
image

David_P1
Participant V

Also looking for a solution to this use case. How to implement a table calculation for running total that recognizes different values in the category dimension (so that it resets the count per category)??

@adeel_nazir the sorting is handled directly within the results. You can even sort by several columns. The key is to find out how to create the table calculation for the running total…

jpklwr
Participant II

I think you would just pivot the table by the “category” dimension then the running total calculation would calculate for each measure in the series.

Hi @adeel_nazir,

If I understood correct, Are you looking for result like

Cat | RunningTotal

1| 60

2|100

3|10

If yes then , if you declare runningtotal as measure in your view file then looker will take care of it.

e.g.

 

measure: runningTotal{

type: sum

sql: ${TABLE}.“runningTotal” ;;

}

Hey @adeel_nazir / @David_P1 were you guys able to achieve what you’re looking for? I am also looking for a running total that resets by category., i.e., I want running that that starts at 1st of every month, goes until 31st and it should reset the calculation from the 1st of the nest month. Can you please help if this is achieved using lookml?

genghisk
Participant I

@adeel_nazir , @vkathepalli 

I solved this using just LOOKER table calculations.

your category has to be a number, if not already  you can change it into first row occurrence by match.

Category number: 

match(${category},${category})

Create new table calc column for running total

running_total(${ammount})

The first category’s running total by default will be  correct, so we just need to adjust it for all other categories. 

Create new table calc for Adjusted Running Total column, if its not the first category. 

if(${category}=1,${runningTotalAmount},
${runningTotalAmount}-offset(${runningTotalAmount},-(${orderdate})))

Your “order date” needs to be in relative terms of 1,2,3,4 etc. So the data need to be sorted first and then create a row()-match(${category},${category})+1

7f6d0b14-37f0-46bc-ae09-a05c9d5fac48.png

hnesman
Participant I

Hi genghisk

I can’t understand your answer.. could you explain further please? I think you got what I’m looking for.

I particularly don’t understand where you define this? Is this something to add as a Table Calculation or as a Measure?

Category number: 

match(${category},${category})

And then I don’t see you used it in the Table Calculation

if(${category}=1,${runningTotalAmount},
${runningTotalAmount}-offset(${runningTotalAmount},-(${orderdate})))

Thanks very much,

Horacio

genghisk
Participant I

Hi genghisk

I can’t understand your answer.. could you explain further please? I think you got what I’m looking for.

I particularly don’t understand where you define this? Is this something to add as a Table Calculation or as a Measure?

Category number: 

match(${category},${category})

And then I don’t see you used it in the Table Calculation

if(${category}=1,${runningTotalAmount},
${runningTotalAmount}-offset(${runningTotalAmount},-(${orderdate})))

Thanks very much,

Horacio

Hey sorry for the unclear naming and steps.

  1. Sort data by group and date in desired order. (In my example, I'm removing HIPAA patient IDs information and re-naming it “group” for simplicity, this is also helpful in step 4)
  2. Create running total table calculation, this will do running total for all records.
    1. running_total(${number})
  3. Create table calculation to get the occurrence of each group
    1.  row()-match(${group},${group})+1
    2. current row minus first occurrence of that group, this gets you 1st, 2nd, 3rd occurrence per group
  4. Create Table calculation to adjust the “running total” calculation in step 2
    1. if(${group}=1,${running total},
        ${running total}-offset(${running total},-(${Occurrence order})))
    2. If the group is the first group, do running total as normal, but if its not the first group  subtract the running total by the offset of each group to cancels out and gives us the running total per group.
    3. Without the if statement, the running total would start at the second group of data. 
07ea9fa4-362c-468e-82f9-24fad83e8e1d.png

Hi @genghisk this works perfectly but I need to tweak this a little bit so that it is calculating percent of total of each category. Any Idea how should I tweak this so that it can calculate percent of total?

 

Thanks a bunch.

Unfortunately, I have not found a solution to this. I needed this as well. This would work if we could set a parameter of a match type that output the last occurrence (like excel). 

Hi @genghisk I think I found the solution.  Got the inspiration from here .

First I find the sum of the total from each category, just like the window function in SQL. Then I create a table calculation dividing the original count column and the total window column. And it worked fine. 

glad this worked for you, it would not for me bc i cannot edit the LOOKML. I was hoping to do it with just table calculations alone. 

hnesman
Participant I

Hi @genghisk, got it perfectly now, that’s very helpful.

Thanks!

Top Labels in this Space
Top Solution Authors