selecting last row in beastmode

IVG

So I have a dataset which has a column with a value that updates every 10 minutes. I would like to make a comparative gauge that will compare last value from today vs the last value from yesterday - i can't figure out the two beastmodes to create those values... any ideas? Thanks in advance!!!

IVG

ok so I had a colleague write some SQL that made it work... so I will show you here just as a point of interest...

 

SELECT (
SELECT Value
FROM dataset_1

WHERE date = (
SELECT min(date)
FROM dataset_1

WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
)
) as YESTERDAY,
(
SELECT Value
FROM dataset_1
WHERE date = (
SELECT max(date)
FROM dataset_1
WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
)
) as TODAY

FROM dataset_1
LIMIT 1

 

So this took care of all the issues - the result was that I got two values one from today and one from exactly 24hours ago in two separate columns - which made it perfect for using in a comparative guage!

PodiumMason

Hi @IVG

 

Referencing the documentation in the knowledge base: http://knowledge.domo.com?cid=comparativegauge, I think this could be possible:

 

I think what I would do is a couple date beast modes to align the data so for both yesterday and today we're looking at the same date:

 

Date filter to filter on dates less than now: Case when `datetime` <= Now() then 'Yes' else 'No' END

Date alignment (order by this column descending): Case when date(`datetime`) = Current_date() then `datetime` else  Date_add(`Datetime`, Interval 1 Day) end

 

Current value on the gauge will be the value column. 

Previous value will be a beast mode: Case when date(`Datetime`) = date_add(Current_date(),Interval -1 Day) then `Value column` else 0 END

 

Filter card to today. Current value value will be the `Value Column`, Previous value will be the `Previous Value` beast mode mentioned above.

 

Hope this is helpful for a direction to go, this is just the first thing that came to mind when you described your problem, let me know if it works or if you have addtional questions.

 

Best of luck!

IVG

Either I am doing something incorrectly or this is not working...

I have created the 3 beastmodes you have suggested the problem i get a the current value as the last value in the "Value Column" but the Previous Value column is empty...

 

I think its' becuase the filter for today's value cuts out all the previous values so the there are no values from before "today"... 

 

but not sure... Thanks for the help, but still, I have no idea how to do it right ?

PodiumMason

@IVG Maybe try removing the today filter on the card and see if that works? With the ordering of the data, that might fix it not showing the previous values.

IVG

now the previous value column is showing the same value as the current value column, i am hopelessly confused ...

IVG

I can't imagine this being such a difficult thing to do - all i want to select is most current value and one last value from a previous day... its seems like there should be a very simple way to do it.

 

As an alternative I am ok with selecting the latest current value and one 24 hrs before it... 

 

Please help!

 

PodiumMason

@user00421 

 

I figured out the problem with my logic, the bad news is that the comparitive gauge won't accept the fix. 

 

The date alignment column is correct, the current value needs to be a beast mode:

 

SUM(Case when date(`DateTime`) = CURRENT_DATE() then `Value` else 0 END)

 

and the Previous value needs to be aggregated:

 

SUM(Case when date(`DateTime`) = date_add(current_date, Interval -1 day) then `Value` else 0 end)

 

This gets the data in the correct format when the data is ordered by datetime descending. (See screenshot)

 

The data is in the correct format at the table card level, but when I try to order by the date alignment in the comparative gauge card and use the pre-aggregated values, I get an error, unable to load the card. It seems that the card type doesn't agree with aggregated values.

 

I can't seem to get around this in card builder. Doing this outside card builder would be fairly simple, but that's not the answer you were asking for.

IVG

So my problem is that once I follow your advise and set it up and have a beastmode for Current Value and Beastmode for Previous Value - I get the followign table...

 

essentially I get a column with current values that correspond to todays date that appear in that date's row and same for previous value but since the numbers are not in the same row it is not working...

 

 

PodiumMason

@IVG That's exactly right. The only way I know to line up the values is by aggregation but it seems the comparitive gauge card doesn't support beast mode level aggregation.

 

I would see if @domosupport on buzz or [email protected]  has any suggestions. It's possible someone else on the board may have an idea on how to solve this in cardbuilder.

 

If you want to build a solution outside card builder, that would be simpler in this case I think based on my testing.

IVG

I am trying now to do it in ETL - by first normalizing the date column with the "set column type" command and then grouping the days using "last value" command - the only problem is that once the time stamp is removed from the date it screws up the order since it is not selcting the last value.... so still trying to work this one out...

IVG

nope it is not working in ETL - can figure this out - I am so stumped - is there anyone out there who can help!? this is driving me nuts!

Please!

PodiumMason

@IVG 

 

Do you know SQL and have access to dataflows? I can help you there if you do. 

 

Otherwise, Domo support is always available to help. 

IVG

i have the access to the dataflows but I am a complete novice with SQL... I have been trying to figure out how to do it with SQL for last hour but unsuccessfully ? so any help will be much appreciated!

IVG

oh and domo support cut me off - i am using a free account and apprently there is a limit to how many times you can ask for help... 

PodiumMason

@IVG

 

So you'll want to create a dataflow in MySQL and do the following transform. When you have created the transform, output it with a Select * from transform_1 as a new dataset (name it whatever you like).

 

Select
Case when date(`datetime`) = Current_date() then `datetime` else Date_add(`Datetime`, Interval 1 Day) END as `date alignment`,
SUM(Case when date(`DateTime`) = date_add(current_date, Interval -1 day) then `Value` else 0 end) as `Previous Value`,
SUM(Case when date(`DateTime`) = CURRENT_DATE() then `Value` else 0 END) as `Current Value`
from last_value_test
group by `date alignment`
order by `date alignment` DESC

 

 

when you build the card, be sure to use the date alignment filter to filter down to 'YES' (as a beast mode):

 

Case when `datetime` <= Now() then 'Yes' else 'No' END

 

 

Let me know if this works for you!

IVG

again thanks for all the help but, a couple of follow up questions:

 

So I managed to get the output following your instructions but I am getting the following result (see below)

and that filter your suggested in beastmode gives "YES" to every line... so there is nothing to filter...

 

PodiumMason

@IVG based on what you told me about your data, the filter was to make sure that when we aligned the dates that you wouldn't have any that are after the current moment in time. 

 

For example if it was four o'clock you wouldn't see any yesterday results from five or six o'clock. (that don't have today's results yet). 

 

IVG

ok so I had a colleague write some SQL that made it work... so I will show you here just as a point of interest...

 

SELECT (
SELECT Value
FROM dataset_1

WHERE date = (
SELECT min(date)
FROM dataset_1

WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
)
) as YESTERDAY,
(
SELECT Value
FROM dataset_1
WHERE date = (
SELECT max(date)
FROM dataset_1
WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
)
) as TODAY

FROM dataset_1
LIMIT 1

 

So this took care of all the issues - the result was that I got two values one from today and one from exactly 24hours ago in two separate columns - which made it perfect for using in a comparative guage!

[Deleted User]

Nice work @IVG on figuring this out!

Kudos to @PodiumMason as well.

 

Regards,

 

PodiumMason

@IVG looks good, glad you got a solution that worked for your dataset!

 

My sample dataset that I was using was definitely simpler and easier to work with it seems like.