I have a formula field I need to create in Data flow using a Compute expression.

It needs to group by our 'Record_ID' and compare this weeks data to last weeks.

The data i want to compare is labeled 'Active_Users' and I want to arrive at a health score based on 'Active_Users' value this week compared to last week.

The dataset is pulled weekly and stamped on import, so I have a date field called ‘Stamp’ which I then use to determine how may 'Active_Users' were active in that week.

The calculation basically needs to be: New Value / Prior Value * 100

I can figure out the rest from there.

I've tried using the formulas below with no success, I think I've definitely left out the part where i need to group the 'Record_ID'.

I hope all that made sense. Any help would be great!

((case when 'Stamp_Week' == "02" Then 'Active_Users' end) / (case when 'Stamp_Week' == "03" Then 'Active_Users' end)) * 100

(('Stamp' == (TODAY()-7) then 'Active_Users') / ('Stamp' == (TODAY()-14) then 'Active_Users') * 100

(((partition by 'Record_ID' order by('Record_ID','Stamp_Week')(case when 'Stamp_Week' == "01" then 'Active_Users' else 0 end))) / (((partition by 'Record_ID' order by(case when 'Stamp_Week' == "02" then 'Active_Users' else 0 end))) * 100

case when 'Stamp' == date('2019','03','17') then 'Active_Users' / 'Stamp' == date('2019','03','10') then 'Active_Users' * 100 end

(A - sum(A) over ([-1..-1] partition by 'FirmId' order by ('FirmId','Stamp_Year~~~Stamp_Month~~~Stamp_Day')))/ (sum(A) over ([-1..-1] partition by 'FirmId' order by ('FirmId','Stamp_Year~~~Stamp_Month~~~Stamp_Day'))) *100

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.