Create calculated field from grouped data

Question & Answer
, , , ,
1

Lets say my dataset reads

Names Duration
Name 1 0.5
Name 2 1
Name 1 1.5
Name 1 0.2
Name 3 4.5

Now if I group then by names, my output will be

Names Duration
Name 1 2.2
Name 2 1
Name 3 4.5

But I want the duration to be in hr and min, i.e,

Names Duration
Name 1 2hr 12 min
Name 2 1hr
Name 3 4hr 30 min

So how do I achieve that output?

2

Hi @paruanil

You could try to handle both sides separately.

  1. convert the integer into a string.
  2. locate the decimal sign.
  3. Create a SubString for the left side and concat the “hr”
  4. Create a SubString for the right side
  5. Convert the right side into integer.
  6. Multiple with 6,
  7. Convert it into string
  8. Concat the “min”
  9. Concat both sides.

BR

1 Like
3

Hi @ErikG
This calculated field which converts it into a string will be happening at a pre grouping level, so once the grouping happens, it cannot give me the final output as 2hr 12 min right?

That’s the caveat I’m trying to overcome.

4

@paruanil
Hello, I created a “translator” to solve my situation. Basically, I added an action when clicking on the table. Where I direct the value to another visual element with personalized narrative. Sorry for my English. Hahaha

image
image812×355 49 KB

Hour:

concat(
    ifelse(
        floor(TotalAggregate.totalAggregate.value) < 10, 
        concat('0', toString(floor(TotalAggregate.totalAggregate.value))), 
        toString(floor(TotalAggregate.totalAggregate.value))
    ),'h'
)

Minute:

concat(ifelse(
    floor((TotalAggregate.totalAggregate.value - floor(TotalAggregate.totalAggregate.value)) * 60) < 10, 
    concat('0', toString(floor((TotalAggregate.totalAggregate.value - floor(TotalAggregate.totalAggregate.value)) * 60))), 
    toString(floor((TotalAggregate.totalAggregate.value - floor(TotalAggregate.totalAggregate.value)) * 60))
),' minutos'
)

image

Certainly, there might be an easier way to do this, but I only managed to do it this way hahaha. I hope it helps you.

6

Hi @Telski I’m sort of new to QS. How to direct a value to another visual element?
What exactly should be my steps?

7

@paruanil ,
Firstly, I apologize for the delay in responding.

Essentially, you need to create an insight:

image
image334×840 26.8 KB

Then, customize it by adding a ‘TotalAggregate’ calculation and selecting your time field. Next, go to the ‘insert expression’ editor and paste the first code. After that, insert another new expression and enter the second code. Your translator will be ready.

If you only have a ‘NAME’ column, you will need to create just one parameter, for example, ‘pName’.
.

image
image530×540 35.3 KB

Now, you will need to add an action to your spreadsheet. Something like this:

image
image369×521 19.3 KB

Click on the plus sign to add the ‘pName’ parameter and then select the NAME field.

image

Finally, and no less important, return to your “translator” and add a NAME filter, then link it to the ‘pName’ parameter, and then it will be ready :smiley:

Again… I apologize if something doesn’t make much sense, but my English is not the best… But I hope it helps you. :sweat_smile:

I learned how to create a dashboard in the demo, here’s an example:

Time

8

Wasn’t able to understand where to get this…

9

@paruanil
here:
image

10

image
Thanks for the snip. This is how my interface looks. Tried to check for it everywhere, but wasn’t able to navigate to the snip you have shown :sweat_smile:

11

@paruanil

I made a video trying to show the process:

I hope it helps you.

1 Like
12

Você conseguiu resolver o seu problema amigo?

13

Hi @paruanil, I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)