topic Re: Aggregate Challenge in QlikView

Aggregate Challenge

marcel_olmo — Tue, 29 Oct 2013 18:08:23 GMT

Hi guys, I have one table with two fields "total old" and "total new", and I want to turn "Total Old" in "Total new":

Here's my current table :

And this is my desired result :

What I'm trying to do is if I have four rows like this :

Date	Num
20/02/2013	1
20/03/2013	2
20/04/2013	1
20/05/2013	4
20/06/2013	6

Get a table like this (with more fields related ) :

Date	Num
20/02/2013	0
20/03/2013	0
20/04/2013	0
20/05/2013	0
20/06/2013	14

This is my current formula, but it doesn't work :

If(

MaxString(Years)

below(Total MaxString(Years))

MaxString( [Km Group] )

below(Total MaxString( [Km Group] ))

Sum(Total<Years, [Km Group] > [Total Old] ),' ')

Here I attach my qv document.

Regards, Marcel.

Re: Aggregate Challenge

Anonymous — Wed, 30 Oct 2013 14:04:49 GMT

Hi Marcel,

i don't know, what you want exactly. Can you describe your problem, again.

Stefan

Re: Aggregate Challenge

marcel_olmo — Wed, 30 Oct 2013 14:11:29 GMT

Hi Stefan, I have two fields : "total old" with my results, and "total new" with my desired results.

What I need is to make an aggregate sum of the previous records of the field "code", and show it only in the last possible result.

In the document you'll see three different 3 tables :

-My result : Is the results that I'm getting in my current table.

-My desired result : Is the desired results, but using the field "total new", and I want to use "total old".

-What I'm trying : I'm trying to replicate the results of the "desired result" table into my current table.

Hope you understand now what I'm trying to do.

Thanks for helping me Stefan.

Regards, Marcel.

Re: Aggregate Challenge

marcel_olmo — Wed, 30 Oct 2013 20:06:01 GMT

Anybody here has any idea of how can I do this with Qlikview? Maybe this is not possible, and I'm wondering if I can do it or not.

Regards, Marcel.

Re: Re: Aggregate Challenge

danielrozental — Tue, 05 Nov 2013 20:08:23 GMT

Here's my take, but I think there are some problems with your Total New field calculation, ie

Code	Date	Km Group	Total New	Total Old	Years
Z1PZAR019629	30/08/2010	+-90	0,00	936,35	1 Año

Re: Aggregate Challenge

marcel_olmo — Tue, 05 Nov 2013 20:28:22 GMT

Thank you Daniel! You got very close, but it's not the same. You have to get the same result as "total new".

I got a suposed good result in a previous post thanks to Bryan Dunphy in a previous post :

http://community.qlik.com/thread/97411

Could you take a look at both documents? Maybe you got inspired as well.

Thanks for trying it!

Regards, Marcel.

Re: Aggregate Challenge

danielrozental — Tue, 05 Nov 2013 20:32:42 GMT

Marcel, I don't understand why my solution is wrong. I believe the values loaded as "total new" are wrong in some cases like the one I posted before.

Re: Aggregate Challenge

danielrozental — Tue, 05 Nov 2013 20:41:13 GMT

Why is the Total New here correct? Shouldn't the value be in the last date?

Code	Date	Km Group	Total New	Total Old	Years
Z1PZ9R041060	11/03/2009	0-30	0,00	40,25	1 Año
Z1PZ9R041060	24/08/2009	0-30	0,00	74,07	1 Año
Z1PZ9R041060	05/08/2010	0-30	493,27	289,02	2 Año
Z1PZ9R041060	20/08/2010	0-30	0,00	89,93	2 Año

Re: Re: Aggregate Challenge

marcel_olmo — Tue, 05 Nov 2013 20:41:46 GMT

No, they are not wrong, but simply they have no logical sense in Qlikview.

The point is that in total new, you have the maximum value possible by date and "code" and you have a sum(total) in the last found value.

The easiest way is to build another aggregate table with the values calculated, but my datamodel is very complex and I'm trying to do it by set analysis.

If you take a look at "Bryan Dunphy" qlikview document, you'll see you have the same case as me, but for a reason I can't explain, it works for him, but not for me.

Here you have Bryan's qlikview document.

Regards, Marcel.

Re: Aggregate Challenge

marcel_olmo — Tue, 05 Nov 2013 21:44:52 GMT

Yes! In that case it is not well calculated, but I hope this is an isolated case.

Regards, Marcel.

Re: Aggregate Challenge

Oleg_Troyansky — Sun, 17 Nov 2013 16:52:20 GMT

Marcel,

I compared your document with the Dunphy document, and I think I understand why one document works and another doesn't...

In the working document, the pivot table has 2 dimensions and the expression calculates the total for one of the dimensions. Hence, visually it shows zeros for all the lines except for the last line, where it calculates the overall total.

In your document, however, you have 2 dimensions, and your expression is comparing both dimensions and calculates the total for both dimensions. So, in a way you duplicate the way pivot table works by default:

//sum( [Total valor recl. Marca] )

If(

MaxString(Years)

below(Total MaxString(Years))

MaxString( [Km Group] )

below(Total MaxString( [Km Group] ))

Sum(Total<Years, [Km Group] > [Total Old] ),' ')

The IF condition will always render true because each row will hold a unique combination of Years and Km Group, and the formula will always calculate the regular sum, because the Total <> has both Dimensions in it - Years and Km Group.

In order to get the effect similar to Dunfy's, I'd think you want to do something like this:

If(

MaxString(Years) <> below(Total MaxString(Years))

Sum(Total <Years> [Total Old] ),' ')

This should work, unless I misunderstood your requirement.

cheers,

Oleg Troyansky

www.masterssummit.com

If(

MaxString(Customer)

below(Total MaxString(Customer))

MaxString([Item Subgroup])

below(Total MaxString([Item Subgroup]))

Sum(Total<Customer,[Item Subgroup]> [Total Stock Wgt]),' ')

Re: Aggregate Challenge

marcel_olmo — Mon, 25 Nov 2013 10:56:38 GMT

THanks Oleg for your try, I know what you mean, but the point is that I need to get the same result as "desired result" (the table with green colour), and if I do the total only for 1 dimension, I won't get a total by year and kilometer group.

Regards, Marcel.