Average of Last 4 weeks

#1

Hi,

I have a look with the x weeks of data. It has 5-6 measures.

How the looks is:
The first column is a dimension , the weeks are pivoted. There are 5-6 measures. I want average for the last 4 or 7 weeks ( not rolling average). Is that possible , if yes how?

e.g : Week -> 2-Jan, 9-Jan , 16-Jan, 23-Jan ,30-Jan ,6-Feb ,13-Feb & I want the average of these 7 weeks across 20-Feb week.

Thanks,
-Soumya

0 Likes

(sam) #2

Hey @ssoutty68 - if I understand correctly, this is a use case for pivot_index. This table calculation function allows you to target specific pivot columns. In your case, you can target four columns, sum up their values, and divide them by 4. It would look something like:

(
  pivot_index(${measure},1) + 
  pivot_index(${measure},2) + 
  pivot_index(${measure},3) + 
  pivot_index(${measure},4)
) / 4 
1 Like

#3

Awesome, this worked. Thanks a ton Sam for the prompt reply. I am new to looker but have been a heavy Tableau user. Would you be able to guide me on the best resources on stuff I can do with Looker. ( visualizations, table calculations etc)

One other thing I needed help with is that I wanted to show 6-7 measure for 7 weeks. But I want the revenue for all 7 weeks together first and then orders for the 7 weeks next. Right now I see week 1 & the revenue, order etc. ( i want to download and play around with this data hence the ask).

e.g get week 1 , week 2 , week 3 …(pivot) and get revenue for these weeks next to each other and then week 1 , week 2 , week 3 …(pivot) and get orders for these weeks next to each other

Appreciate the support.

Thanks
-SS

0 Likes

(vincent) #4

Hi @ssoutty68,

Glad to hear you are enjoying the Looker experience! Here is a great starting point for table calcs. Since you are using pivots, I would check this out as well. That last doc is a great resource for other types of Looker functions for table calcs too. Here and here are two more resources for creating visualizations with your data. And last but not least, here is a great starting point for exploring data! Let me know if you have any questions on this.

Cheers,
Vincent

1 Like

(sam) #5

To directly answer your last question, that kind of fine grain control over pivot columns isn’t possible - we’ll always show all the measures for the first pivot value, followed by the second, etc.

If you’d like to expand on your use case, I can pass along your desire to swap around columns freely to our product team!

0 Likes

#6

Thanks Vincent. I will check this out.

-SS

0 Likes

#7

That would be great if Looker had that flexibility.

My use case is pretty simple as expressed in my query:
e.g get week 1 , week 2 , week 3 …(pivot) and get revenue for these weeks next to each other and then week 1 , week 2 , week 3 …(pivot) and get orders for these weeks next to each other

Do let me know if I can help further.

Thanks,
-SS

0 Likes

(sam) #8

Thanks for helping out @ssoutty68! Let me rephrase - it’s clear what you want to do, but not why. Do you have a script that works on your exported data that expects a certain format? Or would you find the data more pleasing/meaningful to look at if it was in that format?

We appreciate you going the extra mile to discuss this with us!

0 Likes

#9

Hi team looker,

Help needed please. How can I achieve the look(visualization)

like the one I have attached?

Appreciate the help.

Thanks,
Soumya[Uploading… ](http://)

0 Likes

(vincent) #10

Hi @ssoutty68,

It sounds like what you would like to do can be accomplished by following this discourse. This is assuming that you do not have any pivots in the query.

Cheers,
Vincent

0 Likes

#11

Thanks for the prompt reply Vincent. My bad I didn’t mention in my earlier ask that I need help with how can I get (through calculation) the third column ( last 7 week sales average)

-SS

0 Likes

(vincent) #12

Hi @ssoutty68,

So are you trying to create an average with a table calc that is based on the Sales field? Sorry, a bit confused on what you are trying to do here. What fields do you have already and do you have something in progress?

Cheers,
Vincent

0 Likes

#13

Thanks for replying @vincent. In the pic I attached. I get the columns :weeks and sales but I want to calculate the Average and add that column(like how I have added in my dummy sheet above). So I want to show say for 7-8 weeks data (week column) and against that I want the sales column and the previous 7 week average ( in the Last 7 week sales Average( dummy numbers) column). e.g for 1/9/2017 week i want the previous 7 week average before that week and so on. How do I get the previous 7 weeks average when the data displays only say 7-8 weeks… is this more clear?

0 Likes

#14

to answer your question. Yes i want the 7 week average based on sale. But as I am not displaying weeks before Jan 9 how do I get 7 week average for Jan 9 week or Jan 16 & so on.

0 Likes

(vincent) #15

Hi @ssoutty68,

I see what you’re saying. However, the problem here is that table calcs are performed on the front end once the results are returned from the DB. So if we wanted to do some kind of analysis on the previous 7 weeks of sales, we would have to create a query that returns those values. But lets say that wasn’t the issue and you wanted to get some kind of rolling average, that you can do with table calcs. One way to it would be to use the mean() function with the offset_list() function nested inside, something like this. In this example, when we want a rolling average over a course of a week, we would write something like mean(offset_list(${measure_name}, 0, 7)). What this does is starts at row 1 and does the running average down the rows. We can also do mean(offset_list(${measure_name}, -6, 7))` which will reverse the running total and start from the bottom. Let me know if that helps or you have any more questions on this.

Cheers,
Vincent

0 Likes

#16

Thanks Vincent. This was helpful. Is there a way I can hide some weeks in the column from visualization e.g. say I have 25 weeks data but want to show only 7 in the visualization?

why i am doing this: so that I can use the formula you suggested in your last post rather than go the query route.

Thanks,
SS

0 Likes

(vincent) #17

Hi @ssoutty68,

Of course there is, check out this on it. Basically what we do here is create some kind of condition that checks the date and assigns it either yes or no if that condition is met. Then, we click on the cog for the table calc and select, "Hide No's from Visualization". Going about this way will allow you to return data from the previous 7 weeks to create the new rolling average and hide those previous 7 weeks. Let me know if this helps!

Cheers,
Vincent

0 Likes

#18

Thanks Vincent,

I have attached a doc for your reference. Say my data always has 26 weeks data and I add a column: 7 week avg by adding the calculation - the average formula suggested by you earlier ( mean(offset) one. Now I know only from the 8 week (week:“2016-11-14” in my reference doc) I am getting the correct past 7 week average. How do I hide the previous weeks from visualizations using the tip you shared 3 days ago.

Thanks,
SS

0 Likes

(vincent) #19

I @ssoutty68,

Well, there are two ways to go about this. One is within the edit model for the viz.

The second option with table calcs can be seen below. Assuming we don’t want anything from seven weeks ago from today, we get a date_diff from ${users.created_week}` and today and check to see if it is greater than or equal to 49. If it is, we apply no, if it isn’t, we apply yes.

if(diff_days(${users.created_week}, now()) >= 49, no, yes)

Then click on the cog and select “Hide No’s from Visualization”

Then voila!

Let me know if you have any questions on this!

Cheers,
Vincent

0 Likes

#20

Thanks for the prompt reply @vincent , Appreciate the support. I will try this out.

-SS

0 Likes