Meliorainsights.com
  • Home
  • Tableau CRM
  • Blog
  • Non-Profits
  • Our Clients
  • About
  • Trading Studio

​Meliora Insights, LLC

​Data Cloud | CRM Analytics  (TCRM / Einstein Analytics) Consulting
​meliora - 'for the pursuit of the better'


A blog series distilling quantitative concepts /use-cases in CRM Analytics  (Einstein Analytics).

Picture

word !   :-)

10/25/2022

0 Comments

 
Great article on CRMA vs. Native Salesforce reporting.

​https://marktossell.com/2022/10/22/why-do-you-need-crma/
0 Comments

Whitespace Heatmap (with top"n")

10/4/2022

0 Comments

 
Client needs a heatmap. Legacy dashboard shows only top 3 clients grouped by business unit with top 3 determined by total revenue. However, Client wants to see ALL the revenues of the 3 clients and not just the row containing the revenue that is the top 3. Illustratively, imagine the if the top 3 clients by revenue for the year is Coke, Toyota and Microsoft. Using q=limit q 3 will only show the 3 rows each showing the revenue fo rthe 3 BU's. ex: IBM=$10M, Coke=$9M, Toyota=$7m. Cleints wants to see all the revenues of the 3 and not just the $10m, $9m, $7m . This is accomplisehd by using rank() and partition. It flags the 3 companies and labels them by validAcct and then all the rows that has 'validAcct' is displayed.

WITH BINDINGS
q= load "x_LS_Invoice_Volume";
q1 = group q by ('{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}', '{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}');
q1 = foreach q1 generate '{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}','{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}', coalesce(sum('Revenue'),0) as 'Revenue',rank() over([..] partition by all order by sum('Revenue') desc) as 'rank',case when (rank() over([..] partition by all order by sum('Revenue') desc)) < 11 then  1 else 0 end as 'validAcct';
q2=group q1 by ('{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}', '{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}','validAcct');
q2 = foreach q2 generate '{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}', '{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}','validAcct'as 'validAcct',sum('Revenue') as 'rev', max('validAcct') over([..] partition by '{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}') as 'inSet';
res = filter q2 by 'inSet' == 1;
res= foreach res generate '{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}', '{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}','rev' as 'revenue',case when 'rev' >= {{column(static_1.selection,["Valu"]).asObject()}} then "Above Threshold" else "Below Threshold" end as 'Threshold';
res= order res by 'revenue' desc;

BINDINGS RESOLVED

q= load "0Fb1T000000ob5mSAA/0Fc1T000005jHg4SAE";
q = filter q by date('Date_Year', 'Date_Month', 'Date_Day') in ["current week".."current day"];
q1 = group q by ('Account.Owner.Name', 'LOB2');
q1 = foreach q1 generate 'Account.Owner.Name','LOB2', coalesce(sum('Revenue'),0) as 'Revenue',rank() over([..] partition by all order by sum('Revenue') desc) as 'rank',case when (rank() over([..] partition by all order by sum('Revenue') desc)) < 11 then  1 else 0 end as 'validAcct';
q2=group q1 by ('Account.Owner.Name', 'LOB2','validAcct');
q2 = foreach q2 generate 'Account.Owner.Name', 'LOB2','validAcct'as 'validAcct',sum('Revenue') as 'rev', max('validAcct') over([..] partition by 'Account.Owner.Name') as 'inSet';
res = filter q2 by 'inSet' == 1;
res= foreach res generate 'Account.Owner.Name', 'LOB2','rev' as 'revenue',case when 'rev' >= 5000 then "Above Threshold" else "Below Threshold" end as 'Threshold';
res= order res by 'revenue' desc;
Picture
0 Comments

    Archives

    June 2025
    April 2025
    January 2025
    November 2024
    August 2024
    April 2024
    March 2024
    February 2024
    January 2024
    December 2023
    June 2023
    May 2023
    April 2023
    March 2023
    February 2023
    December 2022
    October 2022
    September 2022
    July 2022
    April 2022
    March 2022
    January 2022
    November 2021
    June 2021
    April 2021
    March 2021

Site powered by Weebly. Managed by Hostwinds
  • Home
  • Tableau CRM
  • Blog
  • Non-Profits
  • Our Clients
  • About
  • Trading Studio