Question

Running Total by Category & Date

  • 31 January 2020
  • 12 replies
  • 3439 views

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


12 replies

Userlevel 1

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

Userlevel 1

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


Here is example

image

Userlevel 3
Badge

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…

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_P 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?

 

@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

 

 

 

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

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. 

 

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

Thanks!

Reply