How do I replicate IFS(CountIF(XXX)>1,IFS(XXX),0) logic in Quicksight?

Question & Answer
,
1

I am trying to replicate an excel formula in Quicksight for billing
Logic - EE01 and XX01 if both on the same check_id for a given entity we only could EE01 as a 1 and XX01 as a 0. check_count in the excel file is a logic field with: =IFS(COUNTIF(“check_id_column”,“check_id”)>1,IFS(“billing_code”=“XX01”,0,“billing_code”=“XX02”,0,)

entity_id check_id billing_code check_count
000000b1939cf31975 1f0552aa9d99 AA03 1
000000b1939cf31975 1f0552aa9d99 EE01 1
000000b1939cf31975 1f0552aa9d99 EE02 1
000000b1939cf31975 1f0552aa9d99 XX01 0

Expected outcome from Excel is EE01 = 1 in check count and XX01 = 0.

How do I replicate this in Quicksight? I know how to count across columns but is it possible to count down rows like this?

2

HI,

Here is the syntax in QS you can use.

if else

For count

Regards,
Naveed Ali

1 Like
3

Hi, @Naveed
I get an error if I try and put countif nested in ifelse or maybe I am doing it wrong how for check_count can I get XX01 to be 0 as EE01 is present?

If I add another row new entity and XX01 that in check_count should get a 1

entity_id check_id billing_code check_count
000000b1939cf31975 1f0552aa9d99 AA03 1
000000b1939cf31975 1f0552aa9d99 EE01 1
000000b1939cf31975 1f0552aa9d99 EE02 1
000000b1939cf31975 1f0552aa9d99 XX01 0
111111b1939cf31988 33332aa9d98 XX01 1

Is there an example you could provide me for check_count column calculation that get’s me the relevant 1 and 0s?

4

Hi @Naveed
Are you able to help please with my latest response?
Regards,
Vanessa

5

@VanessaF, can you please share the expression that is resulting in error if this is not yet resolved.

1 Like
6

Ok so my logic I put these calcs in the dataset:
Step 1. Calc_Eqf_Exp_BillingCodes_1 =
ifelse(
{billing_code}=‘EE01’,‘KYC Cascade’,
{billing_code}=‘XX01’,‘KYC Cascade’,
‘Other’
)
Purpose to group the KYC cascade rows

Step 2: Calc_KYC_Cascade_2
ifelse(
{Calc_Eqf_Exp_BillingCodes_1}=‘KYC Cascade’,countOver({check_id},[{entity_id},{Calc_Eqf_Exp_BillingCodes_1}],PRE_AGG),
0)

Step 3: check_count
ifelse(
{billing_code}=‘XX01’ AND ({Calc_KYC_Cascade_2}>1),0,
{billing_code}=‘XX01’ AND ({Calc_KYC_Cascade_2}<=1),1,
{billing_code}=‘EE01’ AND ({Calc_KYC_Cascade_2}>1),1,
{billing_code}=‘EE01’ AND ({Calc_KYC_Cascade_2}<=1),1,
0)

I’m sure I can do this more effectively

7

Was wondering @DeepakS if you are able to review my logic please?

8

Hello @VanessaF !

If you would still like assistance with this, or if you have a new QuickSight question, feel free to post a new topic in the community so you will be at the top of the priority list for a response from one of our QuickSight experts.

I am going to archive this topic since it has been open for an extended period of time. If you were able to find the solution please feel free to post it to help the community. Thank you!