topic Re: Calculation in straight table in QlikView

Calculation in straight table

Tue, 25 Mar 2014 09:21:18 GMT

Dear Community,

As shown in the sample document, how can show a new column where its available= 1 and sum of that particular type?

so I would like to see sum of typeA, 3+4+3 = 10, sum of typeB = 4 and so on...

Thanks and best regards,

Chanel

Re: Calculation in straight table

alexandros17 — Tue, 25 Mar 2014 09:26:21 GMT

Here it is

Re: Calculation in straight table

sushil353 — Tue, 25 Mar 2014 09:31:38 GMT

Try this:

Sum({$<available={1}>}F2*F3)

Re: Calculation in straight table

Tue, 25 Mar 2014 09:39:35 GMT

You can use the following for calculating sum:

=Sum({$<available = {1}, F1 = {'Type A'}>} F2*F3)

Hope this helps!

Re: Calculation in straight table

sudeepkm — Tue, 25 Mar 2014 09:42:18 GMT

Please take a look at the attachment. I've few questions. Do you just want to show the TypeA in the chart?

Do you just want to show the row where available =1, what about available =0 do you want that ignored in the row in the st Table?

Re: Calculation in straight table

Wed, 26 Mar 2014 02:31:53 GMT

Thanks for the suggestion, but I want to get sum of TypeA which is 3+4+3 = 10, as well as sum of TypeB = 4 in the new column.

Re: Calculation in straight table

Wed, 26 Mar 2014 02:33:09 GMT

Thanks for the suggestion, is closer to solution now, but I want to get sum of TypeA which is 3+4+3 = 10. Is it possible to make it?

Re: Calculation in straight table

Wed, 26 Mar 2014 02:34:57 GMT

Thanks for helping but sorry, the formula is not working with the " F2*F3) “

Re: Calculation in straight table

Wed, 26 Mar 2014 02:41:49 GMT

Thanks for your questions and suggestion. I want to show the row where available = 1, and sum of the type in that row where available = 1.

so in the new column, i would like to see sum of TypeA which is 3+4+3 = 10 and TypeB = 4.

Thanks and best regards,

Chanel

Re: Calculation in straight table

Wed, 26 Mar 2014 03:39:46 GMT

Hi Chanel,

Does this do what you need it to do...

If(F1='TypeA',sum({<F1={'TypeA'}>}TOTAL F2*F3),sum({<F1={'TypeB'}>}TOTAL F2*F3))

Julian

Re: Calculation in straight table

Wed, 26 Mar 2014 03:52:49 GMT

Sorry, Try this one. It does fit the sample data but may not easily scale to fit your full data set...

If(F1='TypeA',sum({<F1={'TypeA'}>}TOTAL F2*F3),

If(F1='TypeB',sum({<F1={'TypeB'}>}TOTAL F2*F3),sum({<F1={'TypeC'}>}TOTAL F2*F3)))

Re: Calculation in straight table

Wed, 26 Mar 2014 05:16:32 GMT

Try this

=Aggr(Sum(F2*F3),F1)

Re: Re: Calculation in straight table

Wed, 26 Mar 2014 06:23:21 GMT

oops!! strangely the symbol got converted, you can try the below modified expression:

=If(F1='TypeA' and available='1', Sum({<F1={'TypeA'}>}TOTAL F2*F3),
If(F1='TypeB' and available = '1',Sum({<F1={'TypeB'}>}TOTAL F2*F3)))

This will not show anything in the rows where available is not 1, you can add 0 or '-' per you requirement.

Hope this helps!

Re: Calculation in straight table

PrashantSangle — Wed, 26 Mar 2014 06:42:24 GMT

Hi,

Check attached File,

Regards,

Re: Re: Calculation in straight table

qlikpahadi07 — Wed, 26 Mar 2014 06:43:49 GMT

Hi chanel,

I am not sure why you need such output where you can easily get with partial sum features.

anyways PFA I have done in all 3 forms may be helpful for you.

Re: Re: Calculation in straight table

lironbaram — Wed, 26 Mar 2014 09:45:40 GMT

have a look at the attachment