Formatting a derived measure that was calculated from multiple datasets

Question

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?

Answer 1

I fully agree that this is strange, but it is the correct answer: You do indeed still add the this as a derived measure in your dataset's XMD. If you have two datasets, I don't think it matters which one you add it in.

So in other words:

1. Click Edit for your dataset
2. Download the XMD file for your dataset
3. Modify the XMD file locally
4. Upload the modified XMD file against your dataset

In your case, the XMD file should look something like this:

"derivedMeasures": [
    {
        "field" : "Variance",
        "format" : {
            "customFormat" : "[\"$#,###.00\",1]"
        }
    }
]