• John Sadleir
  • NEWBIE
  • 0 Points
  • Member since 2017

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 2
    Replies

I am trying to binding Profile_2 and CreatedDate_LastLogi_1 and when i select a month in the top chart i get no result. I have the binding working when i select bottom chart. the top chart readjusts. Can someone advise on how to get this binding working correctly. I need to keep the SAQL queries the same, so this is why I am using the asEquality filter to pass the data through without disrupting the calculations. 

User-added image
User-added image
        "layouts": [],
        "steps": {
            "CreatedDate_LastLogi_1": {
                "datasets": [
                    {
                        "id": "0Fbw0000000g5TRCAY",
                        "label": "E3CasesUserswithTarget",
                        "name": "E3CasesUserswithTarget",
                        "url": "/services/data/v39.0/wave/datasets/0Fbw0000000g5TRCAY"
                    }
                ],
                "isFacet": true,
                "isGlobal": false,
                "query": {
                    "pigql": "q = load \"E3CasesUserswithTarget\";\nq = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in [\"1 year ago\"..\"current year\"];\nq = filter q by 'User.c_User_Region__c' in all;\nq = filter q by {{column(User_c_User_Region___1.selection,[\"User.c_User_Region__c\"]).asEquality(\"User.c_User_Region__c\")}};\n\nq = filter q by {{column(Profile_2.selection, [\"User.GT.Name\"]).asEquality(\"User.GT.Name\")}};\nq = filter q by 'User.GT.Name' in all;\nq = group q by ('CreatedDate_Year', 'CreatedDate_Month');\nq = foreach q generate 'CreatedDate_Year' + \"~~~\" + 'CreatedDate_Month' as 'CreatedDate_Year~~~CreatedDate_Month', unique('CreatedById') as 'unique_CreatedById';\n\n\nr = load \"UsersGlobalTemplate\";\nr = filter r by 'LastLoginDate_Year' in all;\nr = filter r by 'c_User_Region__c' in [\"Continental Europe\"];\nr = foreach r generate 'LastLoginDate_Year' + \"~~~\" + 'LastLoginDate_Month' as 'LastLoginDate_Year~~~LastLoginDate_Month', unique('Id') as 'unique_Id';\n\ns = cogroup q by 'CreatedDate_Year~~~CreatedDate_Month', r by 'LastLoginDate_Year~~~LastLoginDate_Month';\ns = foreach s generate q.'CreatedDate_Year~~~CreatedDate_Month' as 'CreatedDate', r.'LastLoginDate_Year~~~LastLoginDate_Month' as 'LastLoginDate', sum(q.unique_CreatedById) as 'Active Users', sum(r.unique_Id) as 'User base';\ns1 = group s by ('CreatedDate','LastLoginDate');\ns1 = foreach s1 generate s.'CreatedDate' as 'CreatedDate', s.'LastLoginDate' as 'LastLoginDate', sum(sum(s.'Active Users')) over([..] partition by s.'CreatedDate') as 'CreatedBy', sum(sum(s.'User base')) over([..] partition by all) as 'Total Users';\ns2 = foreach s1 generate s1.'CreatedDate' as 'CreatedDate', s1.'LastLoginDate' as 'LastLoginDate', s1.'CreatedBy' as 'CreatedBy', round((s1.'Total Users'/ s1.'Total Users') * 100,0) as 'Total Users %', round((s1.CreatedBy / s1.'Total Users') * 100,2) as '%';",
                    "measures": [
                        [
                            "count",
                            "*",
                            "%"
                        ]
                    ],
                    "groups": [
                        "CreatedDate"
                    ]
                },
                "selectMode": "single",
                "type": "aggregateflex",
                "useGlobal": true,
                "visualizationParameters": {
                    "options": {}
                }
            },
            "Profile_2": {
                "datasets": [
                    {
                        "id": "0Fbw0000000g5TRCAY",
                        "label": "E3CasesUserswithTarget",
                        "name": "E3CasesUserswithTarget",
                        "url": "/services/data/v39.0/wave/datasets/0Fbw0000000g5TRCAY"
                    }
                ],
                "isFacet": true,
                "isGlobal": false,
                "query": {
                    "pigql": "q = load \"E3CasesUserswithTarget\";\nq = filter q by 'User.c_User_Region__c' in [\"Continental Europe\", \"North America\", \"UK&I\"];\nq = filter q by {{column(User_c_User_Region___1.selection,[\"User.c_User_Region__c\"]).asEquality(\"User.c_User_Region__c\")}};\n\nq = filter q by {{column(CreatedDate_LastLogi_1.selection, [\"CreatedDate\"]).asEquality(\"CreatedDate\")}};\nq = group q by 'User.GT.Name';\nq = foreach q generate 'User.GT.Name' as 'User.GT.Name', unique('CreatedById') as 'unique_CreatedById';\n\nr = load \"UsersGlobalTemplate\";\nr = filter r by 'c_User_Region__c' in [\"Continental Europe\", \"North America\", \"UK&I\"];\nr = filter r by {{column(CreatedDate_LastLogi_1.selection, [\"LastLoginDate\"]).asEquality(\"LastLoginDate\")}};\nr = group r by 'GT.Name';\nr = foreach r generate 'GT.Name' as 'GT.Name', unique('Id') as 'count';\n\n\ns = cogroup q by 'User.GT.Name', r by 'GT.Name';\ns = foreach s generate q.'User.GT.Name' as 'Profile', r.'GT.Name' as 'Profile1', sum(q.unique_CreatedById) as 'Active Users', sum(r.'count') as 'User base';\ns1 = group s by ('Profile','Profile1');\ns1 = foreach s1 generate s.'Profile' as 'Profile', sum(sum(s.'Active Users')) over([..] partition by s.'Profile') as 'Total', sum(sum(s.'User base')) over([..] partition by s.'Profile1') as 'Total Users';\ns2 = foreach s1 generate s1.Profile as 'User.GT.Name', round((s1.'Total Users'/ s1.'Total Users') * 100,0) as 'Total Users %', round((s1.'Total' / s1.'Total Users') * 100,2) as '% Profiles Creating Cases';\n",
                    "measures": [
                        [
                            "count",
                            "*",
                            "% Profiles Creating Cases"
                        ]
                    ],
                    "groups": [
                        "User.GT.Name"
                    ],
                    "filters": [
                        [
                            "User.GT.Name",
                            "{{column(lens_1.result, [\"User.GT.Name\"]).asObject()}}"
                        ]
                    ]
                },
                "selectMode": "single",
                "type": "aggregateflex",
                "useGlobal": true,
                "visualizationParameters": {
                    "options": {}
                }
            },
            "lens_1": {
                "datasets": [
                    {
                        "id": "0Fbw0000000g5TRCAY",
                        "label": "E3CasesUserswithTarget",
                        "name": "E3CasesUserswithTarget",
                        "url": "/services/data/v39.0/wave/datasets/0Fbw0000000g5TRCAY"
                    }
                ],
                "isFacet": true,
                "isGlobal": false,
                "query": {
                    "values": [
                        "Origin",
                        "User.c_User_Region__c",
                        "Days_Open__c",
                        "Case_Age__c",
                        "CreatedDate",
                        "Id",
                        "Priority",
                        "Record_Type__c",
                        "SLA_Status__c",
                        "Status",
                        "Type",
                        "User.Name",
                        "User.GT.Name"
                    ]
                },
                "type": "grain",
                "useGlobal": true,
                "visualizationParameters": {
                    "visualizationType": "valuestable",
                    "options": {}
                }
            },
            "User_c_User_Region___1": {
                "datasets": [
                    {
                        "id": "0Fbw0000000g5TRCAY",
                        "label": "E3CasesUserswithTarget",
                        "name": "E3CasesUserswithTarget",
                        "url": "/services/data/v39.0/wave/datasets/0Fbw0000000g5TRCAY"
                    }
                ],
                "isFacet": true,
                "isGlobal": false,
                "query": {
                    "measures": [
                        [
                            "count",
                            "*"
                        ]
                    ],
                    "groups": [
                        "User.c_User_Region__c"
                    ],
                    "filters": [
                        [
                            "User.c_User_Region__c",
                            [
                                "Continental Europe",
                                "North America",
                                "UK&I"
                            ],
                            "in"
                        ]
                    ]
                },
                "selectMode": "single",
                "type": "aggregateflex",
                "useGlobal": true,
                "visualizationParameters": {
                    "visualizationType": "hbar",
                    "options": {}
                }
            }
        },
Hello -- I am new to wave and was hoping someone could help me figure out how to graph a computed field onto a timeline.  I've done some resarch and I believe I need to modify the SAQL to accomplish this, I get lost trying to get the data from the SAQL response into the apropriate grouping and then displayed in a graph.

Background:
My team is doing a survey.  Survey respondents are given a statement and they either agree or disagree with it.  The response is saved as a row in a object with the result of agree/disagree and the date they responded.  The survey runs continously and hundreds of people respond every week.  Response rates are not consistent week to week.

The goal is to create a graph that shows the percentage of survey respondents that agree with the statement grouped by week.  We can then analyze if there is a trend in agreement over time.

I've managed to create a timeline graph that groups responses by week that shows the total count of rows of agreement and disagreement as two seperate lines:
Current State Graph

I'd like to get this to one line that has a percentage (Agree/(Agree+Disagree)) by week.  Because the response rates vary (and may even vary at a greater rate in the future), its difficult to tell the trend at a glance.

The lens I have above I've managed to convert to pigql like what you see on lines 24-31 in the example code on this page:
https://developer.salesforce.com/docs/atlas.en-us.202.0.bi_dev_guide_json.meta/bi_dev_guide_json/bi_dbjson_query_example.htm?search_text=saql

Then the problems start!  When I put groupings into the pigql, operations, or functions they do not make it back into the graph.  I can put groupings and mesure information into the json and if that data is available in the pigql it will graph correctly.  Since you can't have ratios in the json coding (nor the fancy functions available in SAQL) I am a bit stuck here.  I can't get the 'measure' to change from a count to ratio either (y axis).

Any insight greatly appreciated!