You need to sign in to do that
Don't have an account?
Ksenia Choate
Case Statement in Einstein Analytics (Wave) SAQL
I edited SAQL to show month name instead of number in a dashboard step, using a case statement. The steps groups case records by Created Month and limits the data to cases created last and current month. The month name now shows up as expected (e.g., "December" instead of "12"), but the toggle using the step doesn’t facet properly. I am not a developer, and put together the case statement based on examples I found on the internet. I am clearly missing something, and I don’t understand every piece of code I have, so troubleshooting has been very much trial and error, but now I ran out of options.
Here is the code from an out-of the box step that shows month numbers and that works properly:
Here is my code that shows month names and doesn’t facet properly:
And by "it doesn't facet properly," I mean that when I click on the month name in the toggle, all widgets display the "No reults found" message. When I click on the same month name again, it does the same as the "Reset" feature, i.e., returts all widgets into their default position. When I click on the same month name the thrid time, I get the "No reults found" message again, etc.
Could someone please help me understand what I am doing wrong? Any help would be greatly appreciated. Thank you so very much in advance!
Here is the code from an out-of the box step that shows month numbers and that works properly:
q = load "ServiceCase"; q = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in ["1 month ago".."current month"]; q = group q by 'CreatedDate_Month'; q = foreach q generate 'CreatedDate_Month' as 'CreatedDate_Month', count() as 'count'; q = order q by 'count' desc; q = limit q 2000;
Here is my code that shows month names and doesn’t facet properly:
q = load "ServiceCase"; q = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in ["1 month ago".."current month"]; q = group q by 'CreatedDate_Month'; q = foreach q generate (case when 'CreatedDate_Month' in ["01"] then "January" when 'CreatedDate_Month' in ["02"] then "February" when 'CreatedDate_Month' in ["03"] then "March" when 'CreatedDate_Month' in ["04"] then "April" when 'CreatedDate_Month' in ["05"] then "May" when 'CreatedDate_Month' in ["06"] then "June" when 'CreatedDate_Month' in ["07"] then "July" when 'CreatedDate_Month' in ["08"] then "August" when 'CreatedDate_Month' in ["09"] then "September" when 'CreatedDate_Month' in ["10"] then "October" when 'CreatedDate_Month' in ["11"] then "November" when 'CreatedDate_Month' in ["12"] then "December" else "N/A" end) as 'CreatedDate_Month', count() as 'count'; q = order q by 'count' desc; q = limit q 2000;
And by "it doesn't facet properly," I mean that when I click on the month name in the toggle, all widgets display the "No reults found" message. When I click on the same month name again, it does the same as the "Reset" feature, i.e., returts all widgets into their default position. When I click on the same month name the thrid time, I get the "No reults found" message again, etc.
Could someone please help me understand what I am doing wrong? Any help would be greatly appreciated. Thank you so very much in advance!
What was recommended to me and what I ended up doing is creating a new computeExpression node in the dataflow, creating a new field (I named it "Month") within the node, and usuing the case expression within that field. This is the syntax I used:
(I added numbers to the month names so that they are displayed in the correct order on dashboard widgets, otherwise they are ordered alphabetically).
So that is what worked for me.
Alain, thank you again for taking the time to look into this problem!
All Answers
The result is "read only" indeed but have you got the results. Your conversion of months seems correct when I used it.
q = load "case"; q = group q by ('CreatedDate_Year', 'CreatedDate_Month'); q = foreach q generate 'CreatedDate_Year' as 'CreatedDate_Year', (case when 'CreatedDate_Month' in ["01"] then "January" when 'CreatedDate_Month' in ["02"] then "February" when 'CreatedDate_Month' in ["03"] then "March" when 'CreatedDate_Month' in ["04"] then "April" when 'CreatedDate_Month' in ["05"] then "May" when 'CreatedDate_Month' in ["06"] then "June" when 'CreatedDate_Month' in ["07"] then "July" when 'CreatedDate_Month' in ["08"] then "August" when 'CreatedDate_Month' in ["09"] then "September" when 'CreatedDate_Month' in ["10"] then "October" when 'CreatedDate_Month' in ["11"] then "November" when 'CreatedDate_Month' in ["12"] then "December" else "N/A" end) as 'CreatedDate_Month', count() as 'count'; q = order q by 'count' desc; q = limit q 2000;
Alain
What was recommended to me and what I ended up doing is creating a new computeExpression node in the dataflow, creating a new field (I named it "Month") within the node, and usuing the case expression within that field. This is the syntax I used:
(I added numbers to the month names so that they are displayed in the correct order on dashboard widgets, otherwise they are ordered alphabetically).
So that is what worked for me.
Alain, thank you again for taking the time to look into this problem!
You could choose your answer as the best answer if your problem is solved (and post a link perhaps to another forum which explained the solution).
Your first lens could be used in a dashboard with a date selector but your problem was also the order by month which was broken with the labels instead of the numerical values?
The order of months was a minor issue. I mentioned it in the explanation only because month names in the new case statement didn't match what I had in the original case statement, so I just wanted to clarify why.
This was my biggest problem: when I used the step/lens with the original case statement in a dashboard widget, and selected one of the months, I got the "No results found" error in the other widgets, as shown in the screenshot below. Here, I clicked "November" in the toggle widget that was built off a step with the original case statement and the data disappeared from the other widgets:
With the new solution (the new Month field built within the new computeExpression node), when I do the same thing, I actually get correct results, as shown in the screenshot below. Here, I also selected November and the other widgets are now faceting properly:
Have a nice week-end.
Alain
I am using the standard calendar widget. I notice if we make a selection and switch to another Tabs in the same dashboard and we return back it preserve time selection, but not user indicator, wich indicates always "Year".
Does someone know this issue? There is a solution?
Many thanks
case when 'Invoice__c.Invoice_Date__c_Month' in ["01"] then "1 - January" when 'Invoice__c.Invoice_Date__c_Month' in ["02"] then "2 - February" when 'Invoice__c.Invoice_Date__c_Month' in ["03"] then "3 - March" when 'Invoice__c.Invoice_Date__c_Month' in ["04"] then "4 - April" when 'Invoice__c.Invoice_Date__c_Month' in ["05"] then "5 - May" when 'Invoice__c.Invoice_Date__c_Month' in ["06"] then "6 - June" when 'Invoice__c.Invoice_Date__c_Month' in ["07"] then "7 - July" when 'Invoice__c.Invoice_Date__c_Month' in ["08"] then "8 - August" when 'Invoice__c.Invoice_Date__c_Month' in ["09"] then "9 - September" when 'Invoice__c.Invoice_Date__c_Month' in ["10"] then "10 - October" when 'Invoice__c.Invoice_Date__c_Month' in ["11"] then "11 - November" when 'Invoice__c.Invoice_Date__c_Month' in ["12"] then "12 - December" else "N/A" end
Case 'Owner_Region__c'
when “Marketing Queue Lead” then 'Created_By_User_s_Region__c'
else 'Owner_Region__c'
end