Aggregate Challenge

[marcel_olmo]

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))      

    or       

    MaxString( [Km Group] )

    <>

    below(Total MaxString( [Km Group]  ))

    ,      

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

Here I attach my qv document.

Regards, Marcel.

[Anonymous]

Hi Marcel,

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

Stefan

[marcel_olmo]

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.

[marcel_olmo]

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.

[danielrozental]

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

[marcel_olmo]

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.

[danielrozental]

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.

[danielrozental]

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

[marcel_olmo]

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.

[marcel_olmo]

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

Regards, Marcel.