The calculation I want to do is for each month, calculate ColumnC(When ColumnB = ''H2")/ColumnC(When ColumnB = ''H1"). I need to create a calculated field. How can I do that?
Not quite sure I follow exactly what you want to do. Sounds like you want to a calculation based on what’s in column B. Is ColumnC a percent already or does the percent need to be calculated? You’ll likely want something like I have below but I’ll need a little more info before I can help finish the calculated field:
ifelse(
{ColumnB} = H1, [calculation]
)
Hi, column C is not percent. ColumnC is a value, I want to calculate: the value (ColumnC) of H2/ the value (ColumnC) of H1 for each month.
the results should be
I tried:
ifelse({columnB}='H2', {columnC}/ifelse({columnB}='H1', {columnC}, NULL), NULL)
it only gave me NULL values. The problem is H1 and H2 are in the same column but on different rows.
Hi @Bruton3, you might want to per-process this data in your source database to add a new column that will store the H2 values in the newly column but in the same row and then you create the % values accordingly.
Let us know if this solution approach works for you.
Thanks
Deepak
2 Likes
Here is the expression you can use for it
sum(ifelse(ColumnB=“H2”, ColumnC, 0), [Month])/sum(ifelse(ColumnB=“H1”, ColumnC, 0), [Month])
2 Likes
Hi @Bruton3. Did the response from @DeepakS or @Tatyana_Yakushev help you? Let us know if this is resolved. And if it is, please help the community by marking the answer as a “Solution”. Many thanks.
1 Like
Hi @Bruton3 - Hope the response from @DeepakS or @Tatyana_Yakushev were helpful.
I am marking the reply as “Solution,” but let us know if this is not resolved.
Thanks for posting your questions on the QuickSight CommunityQ&A Forum!