topic Re: Using TOTAL with set analysis by Year in QlikView

Using TOTAL with set analysis by Year

zagzebski — Tue, 27 Aug 2013 16:32:57 GMT

I need to do the following. Get a total of ALL sales for a month but for only a specific year:

Data:

Client Month Sales Year

A Jan 100 2013

A Feb 50 2013

B Jan 200 2013

B Feb 100 2013

A Jan 75 2012

A Feb 50 2012

B Jan 100 2012

B Feb 75 2012

Object results:

Client Month Sales-2103 Sales-2012

A Jan 300 175

B Jan 300 175

A Feb 150 125

B Feb 150 125

Basically have to add Set analysis for 2012 and for 2013 to this expression:Sum(total<Month>Sales)... but I must have my syntax wrong because I am coming back with blanks.

Re: Using TOTAL with set analysis by Year

jpapador — Tue, 27 Aug 2013 16:39:23 GMT

Two expressions:

1. Sum({$<Year={2013}>}Sales

2. Sum({$<Year={2012}>}Sales

Make your dimensions Client and Month

Re: Using TOTAL with set analysis by Year

zagzebski — Tue, 27 Aug 2013 16:44:23 GMT

This would not total for ALL sales regardless of the client in the dimension.

Re: Using TOTAL with set analysis by Year

jpapador — Tue, 27 Aug 2013 16:47:23 GMT

Ah I'm sorry. I think what you are looking for is:

Sum(TOTAL <Year, Month> Sales)

Re: Using TOTAL with set analysis by Year

Gysbert_Wassenaar — Tue, 27 Aug 2013 16:48:24 GMT

Try these expressions:

sum({<Year={2013}>} total <Month> Sales)

sum({<Year={2012}>} total <Month> Sales)

Re: Using TOTAL with set analysis by Year

jpapador — Tue, 27 Aug 2013 16:51:07 GMT

Yep was going to add that, Going to still need two expressions if you want separate columns for years

Re: Using TOTAL with set analysis by Year

zagzebski — Tue, 27 Aug 2013 16:51:07 GMT

Close! - but I need to have one column that is just for 2012 and one for just 2013. If I select 2012 and 2013 it will just add them both in this expression. I need two different expressions.