How can I filter the highest value of each hour from the second column(nsu)?
The list is much longer than that, it’s all day.
Hi Andre,
If your “transaction_dt_datetime” field is date format you can group by hour in field well and use max for nsu value. If “transaction_dt_datetime” field is not in date format you need to convert it first to date format.
1 Like
The NSU field is a single number, as if it were a bar code, I put it as a count of distinct to know how many different numbers were generated per hour. If I choose Max, it looks like this in the image adding the unique numbers, I wanted the maximum generated for each hour. Is there a way to do this?
Hi @Andre_Augusto_Vieira, you can also try to add an Hour column in your dataset or analysis like this: toString(extract(‘HH’,{transaction_dt_datetime}). Next, create a Max by Hour calculation. In the calculation, group by the Hour column you created in the previous step: max(Value, [Hour]).
I managed to create the time column but the calculation did not work, the following error appears:
“At least one of the arguments in this function is not typed correctly. Correct the expression and choose Create again.”
Can you put [] instead of {} around the Hour so the calculation looks like this: max({nsu}, [Hour]). Also make sure that Hour is a dimension.
1 Like
Now I managed to create the calculated field, how do I now use this field and bring the result I need?
What happens when you put the date in Group By and Max in Value in the Field wells?
Sorry for the delay to answer you. It still doesn’t work, it shows the error below:
“Getting the data for this view took too long. Repeating this request or importing this data into SPICE may help.”
Hi Andre, no problem. I have a few questions.
- How many records are in the dataset and how many being returned in your report?
- Is this dataset imported in SPICE?
- Is the max calculation based on another calculated field?
- Can you provide a screenshot of the expected result?
- Can you provide sample data?
Hello!
1 - I didn’t understand this question very well.
2 - We don’t use SPICE.
3 - No, they are pure fields. max(nsu,[Hour])
4 - Screenshot below.
5 - The only field I use is the NSU and the date field. The NSU field is given unique numbers (not repeated). I need to count those unique numbers they had per second and know which second of each hour had the highest volume.
NSU field example:
Expected result:
Is tps the count of nsu’s per hour and you want the maximum tps per hour? What’s the data source? Given the error message above, I’m wondering if it would be better to do the max calculation outside of QuickSight. Is there a reason why you can’t import the data in SPICE?
1 Like
TPS is a field that comes from this view:
CREATE OR REPLACE VIEW “vw_tps” AS
SELECT
CAST(transaction_dt AS timestamp) transaction_dt
, year
, month
, day
, “hour”(CAST(transaction_dt AS timestamp)) hour
, “count”(DISTINCT nsu) tps
FROM
rede_cmr_prod_transacoes_db.switch
WHERE (((transaction_code <> ‘014’) AND (establishment_code_id <> 0)) AND (NOT (transaction_type_id IN (9, 11))))
GROUP BY transaction_dt, year, month, day
ORDER BY tps DESC
I would need to create something like this directly in quicksight.
We do not use SPICE per company policy.
Is this query the source for the report?
It seems that we are sending the query properly to your datasource - but its taking it too long to respond back and hitting DirectQuery Timeouts.
Have you tried filtering your data from a shorter date range to see if it responds? are all the filters for transaction code/establishment code being added to the datashoard or customSQL?
I’m also a bit confused about your SQL shared vs the original request. The SQL shared seems to be counting NSU’s while the initial request was for a MAX value in NSU for each HOUR.
To imrans questions - how large is the table being queried here and what is the datasource? Have we tried leveraging SPICE’s computer power as an alternative?
Thanks and regards!
Ramon Lopez