I’m trying to add a ‘Calculated Field’ at a ROW level. For example, I would like to see a columns names “sold” ,Total", “currently at showroom” as rows which was derived as part of calculated field.
|Column 1 | Column 2 | Column 3 | Column 4 |
|Sold| Total| currently at showroom| Removed |
|143570 | 369369 | 35457 | 219799 | |
Could anyone assist me ?
Hello @Parvathy, welcome to the QuickSight community! This is definitely possible, but do you have a column that would contain a value that would tell an ifelse statement which one to show?
Something like this:
ifelse({Profit} > 0, "Sold", {Profit} = 0, "Currently at showroom", {Quantity} = 0, "Removed", NULL)
Then you can add a filter on the visual for that calculated field and select the dropdown that says “Exclude Nulls” so you only see rows where one of the options exist.
You could also add the column in your custom SQL for the dataset and add the row values that way! Let me know if this helps.
Hi DylanM,
Thank you for the reply.
To provide additional context, I have raw file that includes date in the format(dd/mm/yyyy), assigned personnel and a status comment section containing statuses such as as “Sold”, “Currently at Showroom” , "removed ". To remove formatting inconstancies resulting from human errors, I’ve created a separate calculated fields for each of the statuses using Count function and named them separately as “Sold”, “Currently at Showroom” and "removed”. Additionally, I also need to create a new status called "Total Items " which is the combined total of Sold, currently at showroom and removed status.
Now i want the data to show as below :
Hope this helps
Hello @Parvathy, if you want the Statuses to show in the same column, you will have to write the functionality for each value (Sold, Currently at Showroom, etc), in a single calculated field and the same goes for the count of each.
I would recommend using the same functionality to display the Status name you want, as well as the count. I think the contains functionality would be the best for this, I’ll link the documentation here.
Here is my suggested ifelse statement for the Status name column:
ifelse(
contains({status comment section}, "Sold") = true, "Sold",
contains({status comment section}, "Currently") = true, "Currently in Showroom",
contains({status comment section}, "Removed") = true, "Removed",
NULL
)
Here is my suggested ifelse statement for the counts column:
countOver(
ifelse(
contains({status comment section}, "Sold") = true, {status comment section},
contains({status comment section}, "Currently") = true, {status comment section},
contains({status comment section}, "Removed") = true, {status comment section},
NULL
), [Week], PRE_AGG)
I haven’t totally determined the best way to do the totals yet, it is going to be a little tough to exclude the Currently at Showroom section since these columns are being made in calculated fields. Try these two ifelse statements first and lets see if that works.
Hello @Parvathy, I went ahead and marked my response as a solution for now. If you still need help resolving this issue or have a new question feel free to post a new topic so you can show up at the top of our activity log for faster response times. Thank you and good luck!