Not able to convert one Tableau formula

Question & Answer
,
1

Hi Experts,

I am migrating one tableau dashboard to Quicksight and in the process i am not able to convert one of the LOD calculations “{fixed :Sum([Count_Total]) }” in Quicksight, where :-

  1. [Count_Total] => IF [Field Name]=‘Total_Count’ THEN [Count Populated] END.
  2. [Field Name] is a direct string field coming from the data source.
  3. [Count Populated] is a integer type field again coming directly from the source.

Value of [Count_Total] is mostly coming as NULL for us and in Tableau considering that NULL value itself it is able to give some value for “{fixed :Sum([Count_Total]) }” field which is “[Sum_Count]”. So for example for a set of filter values in tableau if [Count_Total] = NULL then [Sum_Count]=1 but for same set of filter values in quicksight if [Count_Total] = NULL then for [Sum_Count] also we are getting NULL instead of [Sum_Count]=1.
Now in quicksight i have used [Sum_Count] = sumOver(sum({Count_Total}),) which i think is the correct corresponding LOD calculation for tableau “{fixed :Sum([Count_Total]) }” calculation, but somehow i am not getting correct result.
Kindly let me know if i am somehow using wrong formula in quicksight for [Sum_Count] or if there is any other issue, thank you!

2

Hi @arpitarorait3016
can you provide a sample with data on row level as well as aggregated, to see the expected and actual values?
BR

3

Hi Erik,

Please find below the data with required information :-

In Tableau In Quicksight
Count_Total Count Populated Sum_Count Count_Total Count Populated Sum_Count
NULL 72 NULL NULL 72 NULL
NULL 58 NULL NULL 58 NULL
NULL 43 NULL NULL 43 NULL
NULL 61 NULL NULL 61 NULL
NULL 55 NULL NULL 55 NULL
NULL 51 NULL NULL 51 NULL
NULL 40 NULL NULL 40 NULL
NULL 28 NULL NULL 28 NULL
NULL 49 NULL NULL 49 NULL
NULL 41 NULL NULL 41 NULL
Aggregated Data Aggregated Data
sum(Count_Total) sum(Count Populated) sum(Sum_Count) sum(Count_Total) sum(Count Populated) sum(Sum_Count)
NULL 498 10 NULL 498 NULL
1 Like
4

Hi @arpitarorait3016

May be sum(ifelse([Count Populated]<>0,1,0)) may give you the result?

5

Hi Giridhar,

Do you mean sum(ifelse([Count_Total]<>0,1,0))?

6

Well I guess you seem to be trying to get a count of records in your dataset. Count populated seems to have a value and it having a value can be considered as 1 count. Count total being null does not make sense to use for the calculation

7

Hi Giridhar,

But as per the logic for Sum_Count field it is performing sum of Count_Total field hence if we take the sum of Count Populated it will change the logic itself, thank you!

8

This looks weird

[Count_Total] => IF [Field Name]=‘Total_Count’ THEN [Count Populated] END

So, basically there is no data coming from your table where the field name = ‘Total Count’ and hence for all rows where you are showing the Count_Total the value is a NULL. Per say NULL is not counted. If you want to count you could probably populate a zero and then SUM it.

So, I am not sure what is the output required; is it a count of records where the field name = ‘Total Count’ or something else?

9

Hi Giridhar,

As we are developing the dashboard in Dev environment so yes as of now all the values we are getting for “Count_Total” is a NULL but the scenario can change in Prod. Can you please provide the quicksight formula based on {fixed :Sum([Count_Total]) } tableau formula where we can convert NULL to 0 and then count it?

10

Hi @arpitarorait3016

You could just change your formula for Count Total as follows to get rid of the NULL

IF [Field Name]=‘Total_Count’ THEN [Count Populated] ELSE 0 END

In your visualization have Count_Total aggregate as Count to get you the desired output. Ideally, I would have considered the value as 1 in the else clause and applied a SUM; but not sure about your intended outcome in case the field value was “Total_Count” (the if clause).

11

Hi Giridhar,

If i am editing the [Count_Total] formula to “ifelse({field_name} = ‘Total_Count’, {count_populated},0)” then i am getting 0 in “Sum_Count” and if using “ifelse({field_name} = ‘Total_Count’, {count_populated},1)” formula then getting 7876 for “Total_Count” which is a wrong value, i should be getting 10 like above data i have provided. PS : using “sumOver(sum({Count_Total}),)” formula for “Sum_Count”.

12

Hi @arpitarorait3016

So, your dataset has 7876 rows which is what is being summarized by a dimension (which is not visible in your screenshot). You could do a distinct count on that dimension column to get the count_total since your summary has 10 rows of output and for each value of the dimension you should be able to get a value of 1 by the distinct count.

13

Hi Giridhar,

count_total has a formula where for most scenarios it is giving output as NULL so not sure which exact field’s distinct count you are asking me to calculate. You can refer all the dimensions i have mentioned in my initial question and let me know which dimension distinct count you are asking me to calculate, thank you!

14

Hi @arpitarorait3016

Please do a distinct count of field_name column and see what you get.

15

Hi Giridhar,

Not getting the required result. Getting distinct count as 357.

1 Like
16

I will need to look at your dataset and analysis.

Please upload to Arena so I can take a look

17

Hi @arpitarorait3016,
It’s been awhile since we last heard from you, if you’re still in need of further assistance, please follow the suggestion above to create an Arena view of your analysis so that we can assist further.

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

18

Hi Giridhar,

Please find the link to dashboard with required data - [Dataset](DHC Dataset)

19

It appears you want a row count. You can create a calculated field row_count (or Sum_Count as you have named it) with the express as 1

That should give you the row count in the visual with a Sum as aggregation.

20

@arpitarorait3016 can see if the solution suggested by @Giridhar.Prabhu works?

Also, I’ve tried the dataset and cannot find the column with NULL value or column name “Count_Total” or “Total_Count”.
can advise which column you are referring to?
Another way is, you can use isNull function to check if the column is null. If yes, can use ifElse function to assign the value as 1.

1 Like