topic Re: Re: Full Accumulation in Expression in QlikView

Full Accumulation in Expression

Wed, 09 Jul 2014 08:46:31 GMT

Hi, there.

I'm a newbie in QV and cannot solve my problem with full accomodation in expression.

Expression looks like this, crucial dimension is Seria:

sum(aggr(RangeSum(above(sum({$<[Kód]={D01TSČ}>}Čiastka),0,RowNo(TOTAL))),Obdobie,Séria,Fáza,Kód,Rok))

I need cumulatives like this BUT IN EXPRESSION !

Thanks in advance.

Duski

Re: Full Accumulation in Expression

MK_QSL — Wed, 09 Jul 2014 08:47:38 GMT

sum(aggr(RangeSum(above(TOTAL sum({$<[Kód]={D01TSČ}>}Čiastka),0,RowNo(TOTAL))),Obdobie,Séria,Fáza,Kód,Rok))

Re: Full Accumulation in Expression

Wed, 09 Jul 2014 09:02:34 GMT

Edited.

Thank You, Manish, for your answer, but your suggestion returns incorrect results 😞

Source data look like this:

Series Plan

Series Uctovnictvo:

Another suggestion ?

Thanks in advance

Duski

Re: Full Accumulation in Expression

MK_QSL — Wed, 09 Jul 2014 09:21:12 GMT

Provide sample data pls...

Re: Re: Full Accumulation in Expression

Wed, 09 Jul 2014 09:32:12 GMT

EXCEL with source data attached.

Thank you in advance, Manish.

Duski

Re: Full Accumulation in Expression

MK_QSL — Wed, 09 Jul 2014 10:01:52 GMT

Try

RangeSum(above(TOTAL sum({$<[Kód]={D01TSČ}>}Čiastka),0,RowNo(TOTAL)))

sum(aggr(RangeSum(TOTAL above(sum({$<[Kód]={D01TSČ}>}Čiastka),0,RowNo(TOTAL))),Obdobie,Séria,Fáza,Kód,Rok))

Re: Re: Full Accumulation in Expression

Wed, 09 Jul 2014 10:14:09 GMT

Thank you, Manish, but ...

- first formula returns same data as above (not correct)

- second formula returns no data 😞

Did you simulate my chart with my source data with correct result ?

Thanks in advance.

Duski

Re: Full Accumulation in Expression

MK_QSL — Wed, 09 Jul 2014 10:30:03 GMT

What are the two expressions you are using in Bar Chart?

Bar Char

Dimension

Obdobie

Expression

RangeSum(above(TOTAL sum({$<[Kód]={D01TSČ}>}Čiastka),0,RowNo(TOTAL)))

is working with me

Re: Re: Full Accumulation in Expression

Wed, 09 Jul 2014 10:35:19 GMT

And the result looks like this ?

Re: Re: Full Accumulation in Expression

MK_QSL — Wed, 09 Jul 2014 10:39:34 GMT

I haven't used aggr...

Re: Re: Full Accumulation in Expression

Wed, 09 Jul 2014 10:43:55 GMT

I meant: is the result for Dec 2014 for Series Plan = 29 000 000 and for May 2014 for Series Uctovnictvo 3 686 616 ?

I do need my chart with this accumulated data:

Thanks in advance.

Duski

Re: Re: Full Accumulation in Expression

Wed, 09 Jul 2014 12:51:22 GMT

Dear Manish, don't give up, pleeeease!

I feel, we are not far away from the goal 🙂

Duski.

Re: Re: Full Accumulation in Expression

MK_QSL — Wed, 09 Jul 2014 12:58:19 GMT

OK... let me know what is the chart you want to use..

what are the dimension ..

how many expressions....

also, if possible, upload your result in excel as a straight table... I will help you out...

Re: Re: Re: Full Accumulation in Expression

Wed, 09 Jul 2014 18:25:14 GMT

Thank you, Manish 🙂

I'd like to see this cumulative chart :

See attached EXCEL - Sheet: Goal1.

Thanx in advance.

Duski

Re: Full Accumulation in Expression

MK_QSL — Wed, 09 Jul 2014 21:48:41 GMT

Sorry for late reply... was busy in my own project.....

Create a Bar Chart

Dimension

Use Calculated Dimension

=If(Year(Obdobie)=Year(Today()),Obdobie)

Expression

RangeSum(Above(TOTAL sum({$<[Kód]={D01TSČ},Fáza = {'B01REAL'}, Séria = {'Plán'}>}Čiastka),0,RowNo(TOTAL)))

and

RangeSum(Above(TOTAL sum({$<[Kód]={D01TSČ},Fáza = {'B01REAL'}, Séria = {'Účtovníctvo'}>}Čiastka),0,RowNo(TOTAL)))

Hope this will solve your problem...

Re: Re: Re: Re: Full Accumulation in Expression

jagan — Thu, 10 Jul 2014 02:34:04 GMT

Hi Dusan,

Please find attached file, hope it helps you.

Regards,

Jagan.

Re: Re: Re: Full Accumulation in Expression

Thu, 10 Jul 2014 06:57:10 GMT

Thank you Manish,

But I think, I make a newbie mistake in the expression writer 😞

When I set both formulas into expression writer, I receive "Garbage after expression 'RangeSum'

What's wrong ?

Thanx in advance.

Duski

Re: Re: Re: Full Accumulation in Expression

jagan — Thu, 10 Jul 2014 07:09:41 GMT

Hi,

I think , is missed in the first line ending, and you need to add bracket at the end of second line.

Regards,

Jagan.

Re: Re: Re: Full Accumulation in Expression

Thu, 10 Jul 2014 07:16:51 GMT

Thank You, Jagan.

I did, but no success 😞

Regards,

Duski

Re: Re: Re: Full Accumulation in Expression

jagan — Thu, 10 Jul 2014 07:21:00 GMT

Hi,

Remove ) at the end of first line and second line.

Regards,

Jagan.