I have columns for each month and an amount on when an expense was recognized, I wanted to add two columns that will give the month and the amount. How do I add these two columns? I can easily create a formula on excel, but I cant seem to do it on QS since my data is pulled directly from a source.
Can you explain more of what your source is giving you that you don’t have?
If you have the month and the amount in your source can you make a table like this?
i wanted to have a column that will pull the month where there was an amount. In the screenshot, I need a column that will say July since the amount of $57.16 was recognized in July.
You can create nested ifelse calculated field for each of the columns. Ifelse - Amazon QuickSight
recognized_month calc field:
ifelse({Jan} <> 0, ‘Jan’, ifelse({Feb} <> 0, ‘Feb’, ifelse({Mar} <> 0, ’ Mar’, …, ifelse({Nov} <>0, ‘Nov’,‘Dec’))))))))))))
Similar definition for the calc field for the recognized_amount.
I am getting an error with my ifelse, is this correct?
ifelse({Actuals2022Jan}<>0, ‘Jan’, ifelse({Actuals2022Feb}<>0, ‘Feb’, ifelse({Actuals2022Mar}<>0, ’ Mar’,ifelse({Actuals2022Apr}<>0, ifelse({Actuals2022May}<>0, ifelse({Actuals2022Jun}<>0, ifelse({Actuals2022Jul}<>0, if({Actuals2022Aug}<>0, ifelse({Actuals2022Sep}<>0, ifelse({Actuals2022Oct}<>0, ifelse({Actuals2022Nov}<>0, ifelse({Actuals2022Dec}<>0)))))))))))))
how do I add the month column on the dataset?
Because the fields Actuals2022Jan are decimals QuickSight expects you to cast it explicitly. Use this syntax
ifelse({Actuals2022Jan}<>0.0, ‘Jan’, ifelse({Actuals2022Feb}<>0.0, ‘Feb’, ifelse({Actuals2022Mar}<>0.0, ’Mar’,ifelse({Actuals2022Apr}<>0.0, ‘Apr’, ifelse({Actuals2022May}<>0.0, ‘May’, ifelse({Actuals2022Jun}<>0.0, ‘Jun’, ifelse({Actuals2022Jul}<>0.0, ‘Jul’, if({Actuals2022Aug}<>0.0, ‘Aug’, ifelse({Actuals2022Sep}<>0.0, ‘Sep’, ifelse({Actuals2022Oct}<>0.0, ‘Oct’, ifelse({Actuals2022Nov}<>0.0, ‘Nov’, ‘Dec’)))))))))))))
Use parseDate to convert a date formatted string to date data type. For Jan parseDate('01-01-2022', MM-dd-yyyy)
will return the Jan date.
i tried this, but it still gives me an error, i even tried 0.0000
i also did change to integer from decimal and tried and still gives me an error
There is a high chance the nested statement have extra closing period. Can you try with a one layer ifelse statement to confirm there are no issues with type casting?
Hi, @bettersinner
Did @saunakchandra’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
it worked, there was an extra “)” at the end. THank you!
yes i was able to put the name of the month in a new calculated field. However, it does not recognize it as a month but as a regular string. How do I convert the column so that it will recognize it as an actual month?
The quickest way is to create a calculated field:
parseDate(
ifelse(
Month=‘Jan’, ‘01/01/2022’,
Month=‘Feb’, ‘02/01/2022’,
Month=‘Mar’, ‘03/01/2022’,
…
Month=‘Dec’, ‘12/01/2022’,
NULL)
, ‘MM-dd-yyyy’)
and then change the date format to show month.
Thank you so much! This worked fine!
Thanks so much for confirming that this has been resolved @bettersinner! @gillepa I have marked your answer as “Solution.”