How do I calculate USD based on Lookup table?

[JYTTEJ]

All our volume are recorded in multiple local currencies

I need to create a report which show our volume in USD.

I have created a look up table which contain the value: CUR EXCH

I have created following search:

index="summary" REPORT=PSVOL Type=Charge|LOOKUP PRJ_table PRJ OUTPUT PRG|lookup exch_rate CUR OUTPUT EXCH |eval Month= if(date_month="may", 5, if(date_month="june",6,if(date_month="january", 1, if(date_month="february",2, if(date_month="march",3, if(date_month="april",4, if(date_month="july",7, if(date_month="august",8,if(date_month="september",9,if(date_month="october",10, if(date_month="november",11, if(date_month="december",12,0))))))))))))|eval USDAMT=AMT*EXCH|  STATS SUM(AMT) SUM(USDAMT)  BY Month Type PRG CUR EXCH

This give following result:

Month   Type    PRG     CUR     EXCH    SUM(AMT)
1   Charge  XXX USD 1   555.6
1   Charge  YYY BRL 1.6022  666.6
1   Charge  ZZZ CAD 0.97675 777.7
1   Charge  VVV EUR 0.700648    888.8
1   Charge  TTT GBP 0.607312    999.9
2   Charge  XXX USD 1   111.1
2   Charge  YYY BRL 1.6022  222.1

Question: How do I calculate the value in USD?

[JYTTEJ · Accepted solution]

Managed to solve the problem myself!:

index="summary" REPORT=PSVOL Type=Charge|LOOKUP PRJ_table PRJ OUTPUT PRG|lookup exch_rate CUR OUTPUT EXCH |eval Month= if(date_month="may", 5, if(date_month="june",6,if(date_month="january", 1, if(date_month="february",2, if(date_month="march",3, if(date_month="april",4, if(date_month="july",7, if(date_month="august",8,if(date_month="september",9,if(date_month="october",10, if(date_month="november",11, if(date_month="december",12,0))))))))))))|stats sum(AMT) BY PRG CUR EXCH Month|rename sum(AMT) AS TOTAMT |EVAL USD=TOTAMT/EXCH | CHART SUM(USD) by Month PRG

[JYTTEJ]

Thanks! The strftime is really great!

[mw]

If you're not already, you'll probably want to utilize a temporal lookup for this as well. That way, you can get the exchange rate at the time of the event, rather than always getting the current exchange rate.

[dwaddle]

A little unrelated, but you might find this an easier to work with way of getting the numerical month number:

eval month_number=strftime(_time,"%m")