Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I am creating an analysis dashboard. Since the currency rate does not include 1 for US Dollars, I need to write an If statement for the sales in US$ across the stores around the world. Since store ID 1,16, 19 are all using US dollars, the expression I wrote is :
If (StoreID=1 or 16 or 19, sum(sale), sum(sale/rate)). But when I double check, say Japanese store, the result returned to me is still sum(sale) instead of sum(sale/rate). Can someone tell me what goes wrong here?
Thank you so much!
Best Regards,
Gloria
if(sum({$<StoreID={'1','16','19'}>}sale),sum(sale),sum(sale/rate))
Your condition is always true.
Try
If (StoreID=1 or StoreID = 16 or StoreID =19, sum(sale), sum(sale/rate))
or
If (match(StoreID,1,16,19), sum(sale), sum(sale/rate))
Or you could convert the Sale values in your script instead, and just do a sum(Sale) in your chart expression.
If doing it in script, either one of swuehl's expression will work and you would just alias it as Sale.
S.
Hi,
Converting in script itself is a good idea, because if you are doing this in expression then you have to write if in all the set analysis expression.
Try like this in script
LOAD
*,
If(Match(StoreID, 1, 16, 19), sale, sale/rate) AS SalesValue
FROM Datasource;
Now in set analysis just use
Sum(SalesValue)
If you don't want to handle this in script then use this expression
If (Match(StoreID, 1, 16, 19), sum(sale), sum(sale/rate))
OR
Sum({<StoreID*={1, 16, 19}>}sale) + Sum({<StoreID-={1, 16, 19}>}sale/rate)
Hope this helps you.
Regards,
Jagan.