Calculated field not working, without error

this calculated field does not show an error message, but it does not work:

ifelse((codsku=‘501’ AND codsku=‘570’), cajasfact, 0)

I am looking for the sales of 2 items grouped.
Does anyone know how to obtain this in another way? or why this does not work?
I would appreciate it if someone could explain it to me at the kindergarten level :slight_smile:
Thanks in advance

Hey sanmar, I would say it is a problem with your aggregation. Did you try to add a text result for testing?

@tb102122 Hi,
Could you please give me an example of what you suggest? Thanks

Ifelse does not work correctly if you use the calculated value in an aggregated table afterwards.
You should use the following function. sumIf - Amazon QuickSight

Sumif(cajasfact, codsku=‘501’ OR codsku=‘570’)

Yes it works like this
This function has the total sales of both codes But I would need to Count the number of customers, who bought -both codes- together. How do I get that?

Do you need the total number of customers or the total sales?
That will depend on your data model but I would assume you can use this for the total customers.

distinct_countIf(<<CUSTOMERID>>, codsku=‘501’ OR codsku=‘570’)

Hi, This shows the total number of customers who bought both codes. That is:

    • customers who bought only code ‘570’
    • customers who bought only code ‘501’
    • customers who bought both codes ‘501’ and ‘570’.
      I need to calculate only this last group (3): Customers who bought both codes together.-

Sorry I am not able to help you further without knowing the data structure.

@tb102122 could you please report the issue to the programmers so that a function can be found to solve that? Thank you

Sorry I think you misunderstood. I would need to know the data structure from your in your analysis. There is no issue in the functionality provided by the Quicksight team.

@tb102122 OK How do I inform you my data structure? What would you need to know about my data structure to understand what I need to solve? Thanks in advance

How does the table look like in your dataset explore.
You can post an image. Please make sure that no confidential data is shown.

@tb102122 Hi
I have a sales structure like this

  • {codsku}=item codes ( ± 800 codes )
  • {cajasfact} = sales
  • {idclientes}=customers (± 10000 clients)
  • fiscal periods (months, quarter, e.g.)
  • Many fields of categories, groups, segments, and salesmen

See the following for the Distinct Count Clients {idclientes}, for 2 items {codsku}, 501 and 570.

I want to know how many clients bought 501+570 togheter in a period (month e.g.)
I hope this are clear for you Thanks in advance

If you only need it with based on similar table like you shared I would use filters. If you have more of these combination you like to visualize I would suggest building up another dimension Customer_segements with PK as customer_id and columns for each criteria. These columns you prepare either with a customer SQL in Athena or during your ETL process.

@tb102122 Yes I need the number of clients in a table , but as I told you at the beginning of my post, the data cannot be obtained through filters . The filters calculate with the OR criterion and I need to obtain the customers who buy from both codes This would be possible using the AND criterion. For everything else you tell me, I wouldn’t know how to do it.

Thank you.

AND will not work either based on my understanding of your data structure, since you have basically multiple lines per transaction in your fact table.
So you basically need to get all rows with yout items ideas and than you need to get the customerids with both transactions.
Are you loading your fact table from Athena?

My database is in redshift

is cajasfact a numeric value? is “codsku” a string? Could you please share the dataset? For ifelse, the if branch and else branch should have the same data type.

Hi @sanmar. Has this been resolved? If so, please help the community by marking the appropriate post as “Solution”. If you still need assistance, please provide additional detail. Thanks!

Yes it has been resolved :blush: