Data Studio
3.4K members online now
3.4K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Last value from a group of values selected via date range (and comparison with previous period)

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi everyone,

 

I'm new in Google Data Studio and I'm in trouble with date ranges and data import. It would be great if you could help me with tis specific situation Smiley Happy

I want to create a Scorecard widget showing data coming from a Google Spreadsheet with two columns:

  1. Date
  2. Score index (imagine, for example, the number of a facebook fan page likes, increasing or decreasing daily)

Basically what I want is to extract the last numeric value from Score Index within a group of values selected with the Date Range tool. For example:

From Last Month values (March, 2017) I need the last of the Score Index values (31st March). Also I would need to compare that value with the value from Previous Period (31st, April).

How I tell Google Data Studio to pick that last score index value from a random selected date range, no matter what period selected, and then compare it with its previous period? I was thinking about creating a custom Calculated Field within my Spreadsheet data source, but I'm not sure about what kind of formula or expression I should use...

 

Hope you can help me Smiley Happy

Last value from a group of values selected via date range (and comparison with previous period)

Follower ✭ ☆ ☆
# 2
Follower ✭ ☆ ☆

Hi Marta, 

Is the last score index always at the end of the month?

Last value from a group of values selected via date range (and comparison with previous period)

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Marta,

 

I wondered if you found the solution because I have a similar issue. So far I changed the aggregation type for 'average' as it's still better than 'sum' but it's not really a solution. I was also thinking about using a function in Spreadsheets or a calculated field but I don't find any function that would actually do this.

Last value from a group of values selected via date range (and comparison with previous period)

Follower ✭ ☆ ☆
# 4
Follower ✭ ☆ ☆

Hi Gregory,

Are you looking to display the last score/result for a given month as well? 

Last value from a group of values selected via date range (and comparison with previous period)

Follower ✭ ☆ ☆
# 5
Follower ✭ ☆ ☆

Creating a max (date) field could work


Or a combination of (last date of the month) field and max (score) field will produce the desired results. 

 

Last value from a group of values selected via date range (and comparison with previous period)

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

Hi Donavyn,

 

I tried creating a max(date) but the scorecard doesn't let you use it as a date range dimension (nor as a filter). Also using the max(score) would just give the highest score and not the most recent. I don't see a way to combine both in a scorecard (I guess a way to do it would be using a table but that's not really what I need). So far the only way I found is to put the logic in my spreadsheet and not in GDS.

Last value from a group of values selected via date range (and comparison with previous period)

Follower ✭ ☆ ☆
# 7
Follower ✭ ☆ ☆

Hi Greg,

could you use some binary logic for the max date or "if last day of the month, then 1 else 0" and then use that as a filter? 

Last value from a group of values selected via date range (and comparison with previous period)

Visitor ✭ ✭ ✭
# 8
Visitor ✭ ✭ ✭

Oh didn't think of that, I guess that would work. My case is a little different as I need to keep the data of the date the closest to today(), so that might not always be the last day of the month but it could work. But the binary logic is brilliant, it might just solve another issue I was having Smiley Wink

Last value from a group of values selected via date range (and comparison with previous period)

Follower ✭ ☆ ☆
# 9
Follower ✭ ☆ ☆

Perfect! I use the binary logic often to get around some of GDS restrictions on dimensions, metrics, and filters.  Good Luck!

Last value from a group of values selected via date range (and comparison with previous period)

Visitor ✭ ✭ ✭
# 10
Visitor ✭ ✭ ✭

I have the same issue. I need to show only the last value.

Let's supposed my table contains "timestamp" and "queue" and I want to see the last queue only.

One way could be creating a computed field called "last_queue" with this formula:

CASE WHEN timestamp =  MAX(timestamp) THEN queue ELSE 0 END

Then aggregate the field with a maximum.

But GDS doesn't allow me to use that as a computed field, it says "Invalid formula.".

My suspicion is that it doesn't want to execute the MAX() inside the WHEN. I have tried creating a new field "max_timestamp" but doesn't help either.

 

Any suggestion?