topic Error during representing aggreagte sum by year using set analysis in QlikView

Error during representing aggreagte sum by year using set analysis

Mon, 13 Jan 2014 10:42:47 GMT

Hi,

I am trying to achieve, sum of "supplier Liability Amount" year by year, using "Set Analysis". In the expression tab, of a bar chart, I am using this:

sum({$<(Year([Book Date]))={'2010','2011'}>} [Supplier Liability Amt])

It is showing error, right at the beginning of the curly braces before '2010'. For this, it does not yield any result. Please find the attached, to get the location of error.
More Info:
- I am not using any dimension in the dimension tab.

- Book Date, that is used in the set analysis expression is of the format MM/DD/YYYY

Kindly help me finding where exactly I am wrong.

Thanks-

Abhishek

Re: Error during representing aggreagte sum by year using set analysis

Anonymous — Mon, 13 Jan 2014 11:14:01 GMT

Hi,

if you writing year(Date Field) then it will write a 2010 and 2011 etc.........

we cant compare in set analysis,we we can compare in if clause like.

=sum(if(wildmatch(year(Date Field),'2010','2011'), Measure Field))

Thanks

SHAIK

Re: Error during representing aggreagte sum by year using set analysis

Nicole-Smith — Mon, 13 Jan 2014 14:59:10 GMT

If you want to do it in set analysis:

=sum({$<[Book Date]={'=match(year([Book Date]),2010,2011)'}>} [Supplier Liability Amt])

Re: Error during representing aggreagte sum by year using set analysis

Tue, 14 Jan 2014 10:54:26 GMT

Hi Nicole. I tried what you said. But that's not working. It is not showing any error, but not giving any result.

Re: Error during representing aggreagte sum by year using set analysis

Tue, 14 Jan 2014 10:59:28 GMT

Hi Khadar. It was to be achieved using set analysis. So, today I tried to make an internal table where, while loading I already extracted year from [Book Date]. That way it left me with Year field which I could directly put into the set expression clause. Thanks.

Re: Error during representing aggreagte sum by year using set analysis

Anonymous — Tue, 14 Jan 2014 14:21:15 GMT

Hi,

if you are using year field inset analysis we can use directly in set analysis,but you are using data field,

please share before what ever the expression you are using before.

Thanks

Shaik

Re: Error during representing aggreagte sum by year using set analysis

bruno_m_santos — Tue, 14 Jan 2014 14:29:28 GMT

I suggest create a new column in script to store the year book.

You 'll gain performance also.

Bruno