Find the equivalent to Tableau’s INCLUDE function in Quicksight

Question & Answer
, , , , , , , ,
1

Hi All,

Is there any way to replicate Tableau’s INCLUDE function in Quicksight using LAC-A or LAC-W functions?

formula in tableau:
(SUM(
{ INCLUDE [Dimension1],[Dimension2]: (SUM([u])/COUNT([a]))
}
))

Thanks in advance!

Regards
kalis

2

Hi @Gayathri, and welcome to the QuickSight community!
Here is a past discussion that dives in to this exact question for you to explore.

If this does not help answer your question, feel free to let me know what further questions you still have remaining.

Thank you!

3

Hi @duncan ,my tabelau formula is here

avg({ INCLUDE [master_bl],[po]: sum(
{ INCLUDE [master_bl],[po]:
(SUM(
IF [confirmed vessel departure]< [booking confirm date] then 0 ELSE
DATEDIFF(‘day’,[booking confirm date],[confirmed vessel departure])
END
)*sum([units_shipped]))
/COUNT([asin])})/sum([units_shipped])})

QS formula:

p3_days=ifelse({confirmed_vessel_departure}<{booking_confirm_date},0,dateDiff({booking_confirm_date},{confirmed_vessel_departure},‘DD’))

sum(p3_days,[master_bl,po])

error: At least one of the arguments in this function does not have correct type. Correct the expression and choose Create again.

Please help me with this

Thank you!

4

Hi @Gayathri, based on the error message you are receiving, somewhere you are using an incorrect datatype. My initial thought is that it relates to the datediff calculation since it expects 2 date fields to run the function. Is ‘confirmed_vessel_departure’ a datetime datatype?

Then, with the sum field, I see you’re trying to partition it by 2 fields, we may need to switch to sumOver instead to run this aggregation. It would also be beneficial when debugging this problem with you, if you could create a demo analysis in QuickSight Arena then shared the link for the published dashboard so I can update the calculated fields and test locally.

Let me know if this helps!

5

TESTING

HERE IS SAMPLE DASHBOARD

EVERYTHING I HAVE CALUCUATED AS PER THE ABOVE FORMULA

BUT NOW TOTAL ARE MISMATCHING(FOR EXAMPLE LAST ROW TOTAL SHOULD BE 13.48[3.23
1.52
8.73]

6

IS IT POSSIBLE TO DO THE SAME CALCULATION IN ANY OTHER WAY.

IF YES PLEASE LET ME KNOW IT WOULD HELP ME ALOT

7

Hi @Gayathri,

It looks like you’re running in to your issue because ‘confirmed vessel departure’ is returning null on some of the rows. So that’s going to break some of your calculations as it will return 0 or nothing.
[Arena View](Find the equivalent to Tableau’s INCLUDE function in Quicksight)

Thank you!

8

Hi @Gayathri,
It’s been awhile since we last heard from you. Do you have any additional questions regarding your original topic or did one of the responses above assist with your solution.

If we don’t hear back within the next 3 business days, I’ll go ahead and mark the solution.

Thank you!

9

Hi @Gayathri,
Since we have not heard back from you yet, I will go ahead and archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information.

Thank you!