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
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
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_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?
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.
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).
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.
Hi @genghisk, got it perfectly now, that’s very helpful.
Thanks!