topic Re: last value in sub sum in QlikView

last value in sub sum

Tue, 14 Jan 2014 12:49:20 GMT

Hi all,

i have a table with the stock values per month e.g.

month	stock value
1	100
2	120
3	150
4	160
5	170
6	100
7	90
8	120
9	130
10	140
11	120
12	90
total	1490

The problem is that the total amount will aggregate all the stock values of the year.

Is it possible to show only the last value in the total amount (90)?

Thanks in Advance

Best regards

Daniel

Re: last value in sub sum

Nicole-Smith — Tue, 14 Jan 2014 14:53:11 GMT

See the expression in the attached.

Re: last value in sub sum

javier_florian — Tue, 14 Jan 2014 15:01:01 GMT

Hi Daniel,

Can you try with...

=Sum({<YearMonth={$(vMaxYearMonth)}>} [stock value])

-JFlorian

Re: last value in sub sum

Tue, 14 Jan 2014 15:57:41 GMT

Hi Nicole,

thx for your answer, but the script doesn't work.

May be the supplier (second dimension) could be the problem.

Re: last value in sub sum

Tue, 14 Jan 2014 15:58:25 GMT

i tried it and lost the game

Re: last value in sub sum

Nicole-Smith — Tue, 14 Jan 2014 16:01:11 GMT

Can you post an example of your chart then? If we don't have the full information, it's hard to help...

Re: last value in sub sum

Tue, 14 Jan 2014 16:20:28 GMT

Hi Nicole,

supplier	department	productgroup	month	stock value
test	women	shirts	1	100
test	women	shirts	2	120
test	women	shirts	3	150
test	women	shirts	4	160
test	women	shirts	5	170
test	women	shirts	6	100
test	women	shirts	7	90
test	women	shirts	8	120
test	women	shirts	9	130
test	women	shirts	10	140
test	women	shirts	11	120
test	women	shirts	12	90
test			total	1490	should be 90 stock value in month 12
test	women	jackets	1	200
test	women	jackets	2	300
test	women	jackets	3	400
test	women	jackets	4	500
test	women	jackets	5	300
test	women	jackets	6	200
test	women	jackets	7	300
test	women	jackets	8	400
test	women	jackets	9	500
test	women	jackets	10	600
test	women	jackets	11	300
test	women	jackets	12	200
test	women	jackets	total	4200	should be 200 stock value in month 12
test	women	total		5690	should be sum of women shirts and jackets 290
test	men	shirts		5690
test	men	shirts	1	200
test	men	shirts	2	300
test	men	shirts	3	400
test	men	shirts	4	500
test	men	shirts	5	300
test	men	shirts	6	200
test	men	shirts	7	300
test	men	shirts	8	400
test	men	shirts	9	500
test	men	shirts	10	600
test	men	shirts	11	300
test	men	shirts	12	200
test	men	shirts	total	4200	should be 200 stock value in month 12
test	total			9890	should be the sum of all productgroups of month 12 (490)

Re: last value in sub sum

Tue, 14 Jan 2014 16:21:01 GMT

it is very hard sorry

Re: last value in sub sum

richard — Tue, 14 Jan 2014 16:39:27 GMT

Daniel,

Is this a table loaded in your script or are you trying to get your result in an object?

Re: Re: last value in sub sum

Tue, 14 Jan 2014 17:15:11 GMT

Hi Daniel,

Please check out the attached document.I created a straight table with the expressions you are looking for.

Hope it helps

Thanks

Re: Re: last value in sub sum

Nicole-Smith — Tue, 14 Jan 2014 17:46:38 GMT

This is about as close as I'm able to get it. QV won't allow me to put partial sums on any of the other dimensions (I think because of the aggr() that I'm using). Hopefully this is a step in the right direction!

Re: Re: last value in sub sum

Nicole-Smith — Tue, 14 Jan 2014 17:50:11 GMT

Your other option would be to calculate these totals in the load script.

Re: last value in sub sum

kiranmanoharrode — Tue, 14 Jan 2014 18:01:57 GMT

Dear Daniel

Simply Create a variable vMaxMonth = Max(Month) in variable Overview

then use below expression in text box

=Max({<Month={"$(vMaxMonth)"}>}Amount)

Regards,

Kiran Rode

+91 8976977897