Is it possible to get the below in 1 row instead of 2 rows, with ProductName 1 showing only AmazonCloudWatch and its revenue under Product revenue 1 & ProductName 2 showing only AWS Audit Manager and its revenue under Product revenue 2?
1 Like
Hello @sarawgia, how are you currently attaching your control value selection with your calculated fields for ProductName1 and ProductName2? Are you trying to directly apply filters or match values depending on what is returning in the parameter selection?
If you utilize parameter linked controls, and use calculated fields, you might be able to bring them on a single row, but there may also be limitations in regards to how your dataset is built. I’ll write an example below:
ProductName1 = ifelse(${ProductName1} = {ProductName}, {Product Name}, NULL)
Then, if your revenue values exist on different rows, you may be able to do something with LAC-W calculations and an ifelse statement.
Product Revenue 1 = sumOver(ifelse(${ProductName1} = {ProductName}, {Revenue}, NULL), [], PRE_AGG)
If you are still facing issues after implementing this, I wonder if you return the value for the parameter in the first ifelse statement instead of the actual dataset value, if that would work better. Let me know if this helps!
Hi @DylanM, I have created the calculated fields as follows -
isProduct1 - ifelse(${ProductName1} = {product_name}, {product_name}, NULL)
isProduct2 - ifelse(${ProductName2} = {product_name}, {product_name}, NULL)
Product Revenue 1 - ifelse({product_name} =${ProductName1}, {product_revenue}, NULL)
Product Revenue 2 - ifelse({product_name} =${ProductName2}, {product_revenue}, NULL)
I created 2 Parameters as below
I also added a Product Filter equal to 1
and the calculated field is : ifelse({product_name} = ${ProductName1}, TRUE,
ifelse({product_name} = ${ProductName2}, TRUE, FALSE))
I did try the above but the revenue numbers were coming incorrect, let me know what I can change to get the values all in one row?
Thanks,
Akansha
1 Like
Hello @sarawgia, I am thinking we may want to utilize LAC-W aggregations to grab the Product Revenue for each and still return them on a single row. Rather than returning Revenue on it’s own in the ifelse statement, we can use sumOver and then aggregate the return value in the field well with min.
RevenueCalc = sumOver({product_revenue}, [{SFDC Customer ID}, {SFDC Customer Name}, {Product}], PRE_AGG)
Then replace the product_revenue field in the return for calculated field in the ifelse statement. I think you may also need to return the name of the parameter for product name rather than the field name, but I would make this change second to see if the first resolves the problem.
ProductName1 = ifelse(${ProductName1} = {ProductName}, ${ProductName1}, NULL)
Let me know if that resolves the issue!
Hi Akansha @sarawgia ,
If @DylanM 's response helped solve your issue, please mark it as the solution.
If you are still having issues, please create a sample in Arena to help community experts assist you better.
Regards,
Arun Santhosh
Pr QuickSight SA
1 Like
@DylanM, thank you, I’ll try your suggested solution, meanwhile, I just used the below formula and repeated it 5 times and got the below results: Product Revenue 1 - ifelse({product_name} =${ProductName1}, {product_revenue}, NULL)
