topic Problem in Set analysis expression in QlikView

Problem in Set analysis expression

Sun, 27 Sep 2015 06:31:23 GMT

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 .

Re: Problem in Set analysis expression

Gysbert_Wassenaar — Sun, 27 Sep 2015 06:49:55 GMT

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])

Re: Problem in Set analysis expression

Anonymous — Mon, 28 Sep 2015 05:18:19 GMT

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

Re: Problem in Set analysis expression

pratap6699 — Mon, 28 Sep 2015 11:37:18 GMT

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)