Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a Data like
OrderDate | Region | Rep | Item | Sales units | Unit Cost | Total Sales |
1/6/14 | East | Jones | Pencil | 95 | 1.99 | 189.05 |
1/23/14 | Central | Kivell | Binder | 50 | 19.99 | 999.50 |
2/9/14 | Central | Jardine | Pencil | 36 | 4.99 | 179.64 |
I want to extract the sales of 2014 by using set analysis in gauge chart, i wrote the expression like as below
sum( {$<Year(OrderDate)={2014}>}[Total Sales])
but it didn't work. Please help me .
You can only use field names on the left side of a set modifier, not an expression like Year(OrderDate).
Try this: sum( {$<OrderDate={'=Year(OrderDate)=2014'}>}[Total Sales])
A better solution is to create a year field in the script, i.e. Year(OrderDate) as OrderYear, and use that in the expression: sum( {$<Year={2014}>}[Total Sales])
You can only use field names on the left side of a set modifier, not an expression like Year(OrderDate).
Try this: sum( {$<OrderDate={'=Year(OrderDate)=2014'}>}[Total Sales])
A better solution is to create a year field in the script, i.e. Year(OrderDate) as OrderYear, and use that in the expression: sum( {$<Year={2014}>}[Total Sales])
Thank you for marking the correct answer here. Gysbert always has good ideas and advice. I hope it's helpful for your progress.
You can also follow top contributors like Gysbert to see some of the other discussions and content they are creating.
Best Regards, Sara
make this date conversion in script level like Load Year(orderdate) as year resident of tablename; chart exp--> sum({<year={2014}>}sales)
[or]
chart exp-->Sum({<orderdate={'=Year(orderdate)=2014'}>}sales)