Hi - I’m looking to see if there is a way to create a double line chart that shows
The format of the sample data is as follows: Month, LoB (Debt Cards, Credit Cards, and Deposit Boxes) , Revenue, Margin.
I’ve tried to utilize an ifelse(locate) function, but it seems that only one categorization can be appended. Not sure if there is another way to do this.
Hi @Gavin,
As I understand, the LOB column has values ( Debt Cards, Credit Cards, and Deposit Boxes ) . You can create create calculated fields based on the required grouping. Use them as part of the line chart.
Can you explain what you are trying to achieve using the ifelse(locate) logic ??
Here is an example
1/ 2 calculated fields created based on grouping1 ( Retail and Tech ) , grouping2 (Energy and Finance )
2/ The same visualized in a line chart .
3/ Calculated field Sales_Retail_Tech
sumIf(Sales,in(Industry,['Tech','Retail']))
Regards,
Koushik
Thanks Koushik.
Simply looking to compare what the net/gross/margin of the total LoBs is against not including one LoB (DB in this case).
Sumif is working for aggregating the total revenue of the LoBs with two calculated fields, but running into issues calculating the margins, which I have as sum(net)/sum(gross), given nesting aggregate functions is not allowed.
Hi Gavin,
I am thinking if level aware calculations ( Create advanced insights using level-aware calculations in Amazon QuickSight | AWS Big Data Blog ) can help solve your requirements, would be helpful if you can paste sample data and provide examples on the expected results.
an example post where sample was provided in table format ( Help with Rank function
Help with Complex Calc!
Hi @Koushik_Muthanna - Apologies for the long winded response but assume the dataset looks something like this:
|Month|Line of Business|Country|Billing|Net|
|2022-10|Debit Cards|US|1.172437496|2.31709386|
|2022-11|Debit Cards|US|1.882804141|1.59266973|
|2022-12|Debit Cards|US|4.570585943|2.975180321|
|2022-10|Credit Cards|US|2.452159697|2.424931535|
|2022-11|Credit Cards|US|3.025037157|1.988792532|
|2022-12|Credit Cards|US|4.266127163|1.495306896|
|2022-10|Deposit Boxes|US|2.953661783|2.125914117|
|2022-11|Deposit Boxes|US|2.197208517|2.467097191|
|2022-12|Deposit Boxes|US|3.215296088|2.583965738|
My usage of ifelse(locate) was to create segmentations of Total Business Margin (CC, DC, and DB) vs Total Business Margin excluding DB (CC and DC). Margin is a calculated field added in with net/billing.
My attempt is below, but it didn’t seem to work as intended:
ifelse
( /* Total*/
locate('Credit Card, Debit Card, Deposit Box',{Line of Business}) > 0,
'Total Business',
/* Exclusion*/
locate('Credit Card, Debt Card',{Line of Business}) > 0,
'Total Business - Excluding DB',
'Null'
)
The output would look something like this:
