Adding a date and an amount column on a dataset

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.

If you want the name of the month you can change how it’s displayed like this.

image

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?

1 Like

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!

1 Like

Thanks so much for confirming that this has been resolved @bettersinner! @gillepa I have marked your answer as “Solution.” :+1: :slight_smile: