topic Using Aggr and Set Analysis in QlikView

Using Aggr and Set Analysis

Thu, 21 Apr 2011 19:00:39 GMT

Hello,

I'm trying to find the sales of this year and compare them to sales of last year but in finding out the dollar value I want to use exchange rate of this year for both years so the difference in the sales amount of the two years is not impacted by variation of exchange rate.

To calculate this year (2011) Sales I have a formula like this

sum( Quantity * Price * Exch Rate)

which is easy. To calculate Last year's sales (2010) This is how I do it:

sum({$<[Year] = {$(=max([Year])-1)}>} Price * Quantity ) * aggr([Exch Rate], PriceListCode)

This works fine but then I have also selected the totals to show and they won't show for the 2010 Sales column. I tried using max instead of aggr and the total shows but then when it calculates it uses the max of exch rate whithin the range of rows and that is not right. Is there a way to accomplish this?

I hope I was able to explain the situation.

Using Aggr and Set Analysis

fosuzuki — Thu, 21 Apr 2011 19:59:38 GMT

Hi Niavarna,

I'm not sure if I understood your problem. But try this:

sum({$<[Year] = {$(=max([Year])-1)}>} Price * Quantity ) * sum(aggr([Exch Rate], PriceListCode))

Hope this helps.

Regards from Brazil

Fernando

Using Aggr and Set Analysis

Thu, 21 Apr 2011 21:00:22 GMT

Thanks for your reply Fernando. The solution you provided does not work however. This way my totals end up being sum of (Price * Quantity) * sum of Exch Rate. However Exch Rate should not be summed up. What I'm trying to achieve is this:

SUM ( sum({$<[Year] = {$(=max([Year])-1)}>} Price * Quantity ) * aggr([Exch Rate], PriceListCode))

so in other words I need the totals of all rows to be summed up but QlikView won't allow the formula above so I'm trying to find a way to do this.

To be more specific. This is the table that I have:

Model Name	PriceListCode	F10 Exch Rate	F11 Exch Rate	F10 Qty	F10 Price	F10 $ using F11 Exch Rate	F11 Qty	F11 Price	F11 $ using F11 Exch Rate
Total		1.3295	1.3295	34	561.99	-	88	866.31	$2,495.21
Knee Cap	AU	1	1	0	30	$0.00	0	45	$0.00
Knee Cap	CD	1	1	3	35	$105.00	3	35	$105.00
Knee Cap	DIRSHIPUS	1	1.05	0	0	-	0	0	$0.00
Knee Cap	DISTCA	1	1	0	0	$0.00	0	0	$0.00
Knee Cap	EE	1.3295	1.3295	0	23.5	$0.00	0	23.5	$0.00
Knee Cap	EX	1	1	34	30	$1,020.00	70	30	$2,100.00
Knee Cap	RB	0.0337	0.0337	0	561.99	-	15	577.53	$290.21
Knee Cap	RBSAMPLE	-	0.0335	0	-	$0.00	0	866.31	$0.00
Knee Cap	US	1	1	0	29.5	$0.00	0	29.5	$0.00

As you can see "F10$ using F11 Exch Rate" column does not give me any totals. If it was giving me totals I expected to see $1,125.00 for the totals.

Thanks

Using Aggr and Set Analysis

Fri, 22 Apr 2011 17:10:52 GMT

How about...


sum({$<[Year] = {$(=max([Year])-1)}>} Price * Quantity ) * only({$<[Year] = {$(=max([Year]))}>} [Exch Rate])

Might want to use max instead of only but I don't think it will matter if those are the dimensions you show. I don't understand why you need to aggr [Exch Rate] over PriceListCode when you are using PriceListCode as a dimension anyway...

Using Aggr and Set Analysis

Mon, 25 Apr 2011 20:52:30 GMT

Thanks Trent,

I used your solution and my total is still blank. If I use max it will take the max of exch rate which is 1.3295 and will multiply every single row by that number. However each row should be multiplied by its corresponding exch rate. I honestly don't want to use Aggr, I'm just trying to find a way to get my totals show up properly. 😞 If I don't use anything and leave it like this:

sum({$<[Year] = {$(=max([Year])-1)}>} Price * Quantity ) * [Exch Rate]

I still don't get any totals.

Using Aggr and Set Analysis

erichshiino — Tue, 26 Apr 2011 02:29:44 GMT

Hi, I'd like to see how the exchange rate field is associated in your data model. Can you provide us a sample application or even a screen shot of you data cloud?

In the mean time, as a work-around, if you are working with a straight table, you can set the behavior or the total.

Go to the expressions tab and change the total from "Expression Total" to 'Sum of Lines'.

Hope it helps.

Rgds,

Using Aggr and Set Analysis

Tue, 26 Apr 2011 03:49:43 GMT

Hi there, try this:

SUM ( ({$<[Year] = {$(=max([Year])-1), $(=max([Year])) }>}

aggr(

sum({$<[Year] = {$(=max([Year])-1)}>} Price * Quantity ) * max(({$<[Year] = {$(=max([Year]))}>} [Exch Rate]), PriceList

)

Regards.