Force table down execution of calculated field instead of table across

Question & Answer
, , , ,
1

Hi,

I have the following calculated field:

(sum(ifelse( {year} = extract("YYYY", now())-1, {cable_delivered_imps}, NULL), [{month}])) +
(sum(ifelse( {year} = extract("YYYY", now())-1, {OTT_delivered_imps}, NULL),[{month}])) +
(sum(ifelse( {year} = extract("YYYY", now())-1, {broadcast_delivered_imps}, 0), [{month}]))

Basically, this calculation is summing up delivered impressions from 3 different columns to get a total amount of delivered impressions grouped by month for the prior year. Quicksight’s default behavior is to execute it as table across then down which is not wrong per say but for my purposes, I need it to execute table down then across.

Is there any way to force it to do this calculation table down across instead of table across down?

2

Hello @azhukova, I have 2 options that may lead you to your desired result. One thought is you can try using sumOver rather than sum. It would look like this:

(sumOver(ifelse( {year} = extract("YYYY", now())-1, {cable_delivered_imps}, NULL), [{month}], PRE_AGG)) +
(sumOver(ifelse( {year} = extract("YYYY", now())-1, {OTT_delivered_imps}, NULL),[{month}], PRE_AGG)) +
(sumOver(ifelse( {year} = extract("YYYY", now())-1, {broadcast_delivered_imps}, 0), [{month}], PRE_AGG))

Another option would be to add new columns into your SQL query to send the sum by month for cable_delivered_imps, OTT_delivered_imps, and broadcast_delivered_imps, then just adding them together in a calculated field.

If that helps you solve your problem in QuickSight please mark my response as a solution, or follow-up with more information you found in the debugging process. Thank you!