I understand how to format a derived measure using the XMD. But here, my derived measure comes from multiple datasets. Here is my SAQL:
q = load "Dataset1"; q = group q by all; q = foreach q generate unique('AccountNumber') as 'AccountNumber'; q1 = load "Dataset2"; q1 = group q1 by all; q1 = foreach q1 generate sum('TargetAmount') as 'TargetAmount'; x = union q,q1; f = group x by all; f = foreach f generate sum(AccountNumber) as 'ActualSum', sum(TargetAmount) as 'TargetSum', round(sum(AccountNumber)-sum(TargetAmount)) as 'Variance', round((sum(Account Number)-sum(TargetAmount))/sum(TargetAmount)*100) as 'Variance%';
I'd like to format Variance as
"$#,###.00". I know I can also add a
number_to_string function to the SAQL, but then my measure becomes a string that I can no longer use it in my number widget.
So do I still try to do this in my XMD? If so, in which dataset (1 or 2) would I edit the XMD for this derived measure?