Find the equivalent to Tableau's INCLUDE 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?

Thanks in advance!

Regards
Tanisha

2

@Tanisha_Shetty Can you please explain the scenario that you have? It will help determine the right solution in QuickSight for the same.

Regards
Vetri

1 Like
3

Hi Vetri,

Thanks for the response!
So, I’m trying to replicate this command:
AVG({ INCLUDE [country_code]: SUM(IF [itunes_avail]==1 THEN [T3M GVWC raw] ELSE 0 END)})

My viz in the form of a pivot table where the listed rows are Tiers:
For instance:
image

So far I’ve tried:

  • sum(ifelse({itunes_avail}=1, {T3M GVWC raw}, 0), [country_code])
  • sum(ifelse({itunes_avail}=1,{T3M GVWC raw},0),[{country_code},Tier])
    and a couple other LAC-A/ LAC-W commands as well, but no luck yet.

Please let me know if you have any leads on this.

Thanks
Tanisha

4

@Tanisha_Shetty INCLUDE is mostly a convenience feature which allows you to use the same calculation in multiple visuals each of which use different dimensions, and not have to make a separate LOD for each one. Something analogous to this is on our roadmap (unfortunately I cannot comment on timelines in this public forum).

You should be able to build a calc that does what you want using QuickSight’s LACs, it’s just you may need a different calc for different visuals if they use different dimensions that you want to include in your partitioning.

For instance have you tried something like this (which in the specific pivot you are showing that in addition to country_code is also showing Tier)?
avg(sumIf({T3M GVWC raw}, itunes_avail=1), [country_code, Tier])

5

Hi Jesse,

Thanks!
Yes, I’ve tried to build a calculated field using this formula and it throws the nested aggregate error. Please find the Screenshot below FYR.

image
image913×189 61.6 KB

Let me know if you know of any workarounds for this, please?

Thanks,
Tanisha

7

Hey @Tanisha_Shetty - sorry that was my fault. You cant put an aggregate (sumIf) inside the LAC (avg). Meant to do it the other way around (put LAC inside the avg). What about this?

avg(sum(ifelse( itunes_avail=1, {T3M GVWC raw},0), [country_code, Tier]))

8

@hafeng Can you please take a peek at this question. Thanks!

2 Likes
9

Hey Jesse,

No worries, thank you :slight_smile:
This formula is valid, however the values seem very off.
For instance, the values I find on QS v/s Tableau.
QS:

image
image1006×244 23.1 KB

Tableau:

image
image1368×195 94.3 KB

Let me know if you can think of what’s happening.

Thanks
Tanisha

10

Your other numbers look like percentages whereas the QS ones are not.

The field you are returning in this calc, T3M GVWC raw, is this a percentage or an integer/float of some sort?

Are all your filters the same on both?

11

Hi Jesse,
I agree, the values I get on QS are not % values.
To confirm, T3M GVWC raw is an integer value / 10000 hence a % figure? And the filters are the same on both Tableau and Quicksight too.

Thanks,
Tanisha

12

Can you break down your calculations in Tableau and QuickSight to see where it is going wrong? Like first do (Tableau first then QS):
IF [itunes_avail]==1 THEN [T3M GVWC raw] ELSE 0 END
vs
ifelse( itunes_avail=1, {T3M GVWC raw},0)

Then:
{ INCLUDE [country_code]: SUM(IF [itunes_avail]==1 THEN [T3M GVWC raw] ELSE 0 END)}
vs
sum(ifelse( itunes_avail=1, {T3M GVWC raw},0), [country_code, Tier])

Then finally the Avg of those.