I created a balance field in compute expression,I want to add a new filed in cmp exp and write SAQL to get balance till end of month any help please?

Question

When I write SAQL query in an Einstein Analytics widget it is working fine but I need to implement the same logic in a field in Compute expression / Compute Relative;

SAQL QUERY IN THE WIDGET:

q=load “dataset”;

##TO GET BALANCE TILL THE END OF PREVIOUS FROM CURRENT DATE##

q_A = filter q by date(‘filedName_Year’, ‘filedName_Month’, ‘filedName_day’) in [..”1 Month Ago”];

##TO GET BALANCE TILL THE END OF 2 MONTHS AGO FROM CURRENT DATE##

q_B = filter q by date(‘filedName_Year’, ‘filedName_Month’, ‘filedName_day’) in [..”2 Month Ago”];

##TO GET BALANCE TILL THE END OF 3 MONTH AGO FROM CURRENT DATE##

q_C = filter q by date(‘filedName_Year’, ‘filedName_Month’, ‘filedName_day’) in [..”3 Month Ago”];

result = group q_A by all full, q_B by all, q_C by all;

result = foreach result generate sum(q_A.’Balanace’) as ‘Balance till the end of previous month’, sum(q_B.’Balanace’) as ‘Balance till the end of 2 months ago’ sum(q_C.’Balanace’) as ‘Balance till the end of 3 months ago’;

So can we get the balance till the end of the previous month in a separate field and Balance till 3 Months Ago in a separate field in "Compute Expression / Compute Relative" so that I can Subtract both?

or

Can I use a Date function in Salesforce to get Balance till the end of Last Month & Balance till the end of 3 Months Ago in separate fields in Salesforce if we already have Balance filed in Salesforce object?

Answer 1

A, I wouldn't recommend that this be done at a dataflow level, because then you have to have this stamped on every record, and would cause inflation of values.

However, if you really need it done, you can do the following. You can use a few methods:

A) Compute Relative. These calculations will go across and allow you to calculate data on many rows. That way you can aggregate.

B) You can create a divergent filter for each of your use cases -> augment them back in back into the original dataflow as a right record -> make sure to "enable multi-lookup" value -> and only select the number you want aggregated.

C) You can use recipes instead, they have a filter and aggregate function that will walk you through the configuration.

I recommend C unless you are dealing with tremendous data volumes.