I need a calculate field in which both date datatype and interger value is present and I want to display it into single row
1 Like
Hello @Siddhi_21, from what I can tell, this is in response to the other question you have posted in the community. Since you have started a new topic, we will close out the previous question.
Based on my initial assessment, there are 2 issues here with helping you find a solution.
We do not have an understanding of the data you are utilizing to build this pivot table, so creating a calculated field without that knowledge will not be possible. We are happy to guide you and assist you with your goal, but we need more information. You will have more success in the future receiving a solution if you provide any steps you have taken to accomplish the goal and possible errors you have faced in the process.
Also, part of your expected solution may not be possible if you are wanting to return different datatypes in a single field. If we create a calculated field, we could return an integer value OR a date value but not both. That is a limitation with QuickSight.
If you would like further assistance with this issue, we are happy to help. But can you please create a demo analysis in QuickSight Arena where we could test some alternative options and provide more detail about the problem you are facing.
Here is where you can find information about QuickSight Arena:
Thank you!
1 Like
this is the field wall i m using
the value is coming from this calculation- lastValue
(
ifelse
(
Label
‘’
,
‘-’
,
Label
), [
Label
ASC], [
Metrics
,
Label
])
Hello @Siddhi_21, so are all of the columns in your proposed solution returning data from the Label field? Each column is returning a label from a different week of the quarter, is that correct? What do you have that defines each week?
Hello @Siddhi_21, so for the metric value In Flight Dates what determines the label value that would return for Transforming the Game and Next Gen Stats?
its already given…in the datafile
Hello @Siddhi_21, so the problem you are going to face is that you are managing string values. If Label was just dates or just integer values, then you could use maxOver. That function would allow you to return a value on all rows within a group.
It seems like you are probably going to need to restructure your data to build out the dataset differently. Instead of having a Sig Notations group column and then the result label across each group and metric, you would probably want a label column for each Sig Notation group. Then your data would look like this:
Metric, Quarter, Label (A), Label(B), Label(C)... etc.
Otherwise, if you are able to create a column for Date values and make them a datetime datatype in QuickSight and another row for Integer values that is also with the corresponding datatype, you may be able to work something out with calculated fields. Mixing multiple datatypes in a single string datatype column will severely limit the functionality available.
can we look the date data as string onlu
Hello @Siddhi_21, you may have some luck with the lastValue calculation. Maybe we could use that to return the label for each metric across sig notations. It would look something like this:
lastValue(
[{Label}],
[{Sig Notation} ASC],
[
{Metrics},
{Quarter}
]
)
That might get you close. The only issue is you have to use it as in the column field well rather than the values field well since it is a string. I am not sure how that interaction will work.
1 Like
We encountered an error trying to save your calculated field. Please try creating it again…error coimg
Hello @Siddhi_21, you can also try making alterations to the calculated field suggestion I made. This is a good chance to learn about the calculations and look at the documentation that I am linking.
My initial guess is that the spelling of the field in the calculation I wrote is slightly different to the actual field name in your dataset. Instead of using copy and paste, try typing out the suggestion and selecting the autofill options that QuickSight provides as you type the solution. Thank you!
Hello @Siddhi_21, we you able to resolve the issue you are facing after updating the lastValue calculation I wrote above? Since we have not heard back from you, I will mark my response as the solution. If you still need assistance on this issue, please let me know and I can guide you further. Thank you!
1 Like
this is not giving me deseried solution…recently i opened one more QA can u check that czo in that i m trying a diffrent method
Date -12 digit integer to a date conversion - #11 by Siddhi_21…
arena version…can u look into it