topic Re: Conditional Total in QlikView

Conditional Total

Tue, 11 Mar 2014 20:08:09 GMT

Hi!

is it possible to have conditional subtotals on the total line?

i wish for example to do the following

two article types;

9 level, ie 90001200

8 level ie 80004000

Pseudo:

if(left(articletype,1) = '8', show 0 in the total row but show row values

if(left(articletyp,1) = '9', show aggregated values in total and show row values.

i have attached my application for better understanding.

Best

Brad

Re: Conditional Total

swuehl — Tue, 11 Mar 2014 20:12:04 GMT

Seems very, very close to your other thread.Please try to avoid posting the same question multiple times.

Again, I would suggest looking into dimensionality() / secondarydimensionality()

Re: Conditional Total

srchilukoori — Tue, 11 Mar 2014 20:18:32 GMT

What is the field name of the article type in the attached sample.

Re: Conditional Total

Tue, 11 Mar 2014 20:20:52 GMT

Lagerartikel_

Sent from my iPhone

Re: Conditional Total

jerem1234 — Tue, 11 Mar 2014 20:28:28 GMT

See if attached is what you want, as swuehl mentions, you need dimensionality:

if(dimensionality()<>1, sum(Round(Aggr(

sum(if([OrderYearWeek] >=

weekyear(today()-(7 * vAntalVeckor))&num(week(today()-(7 * vAntalVeckor)),00)

,[Ordered qty],0))

/vAntalVeckor, Category, Colour, LagerArtikel_, SvItemdesc, [Lager/Risk]), 1)) ,

sum({<LagerArtikel_= {'=left(LagerArtikel_,1)<>8'}>}Round(Aggr(

sum({<LagerArtikel_= {'=left(LagerArtikel_,1)<>8'}>}if([OrderYearWeek] >=

weekyear(today()-(7 * vAntalVeckor))&num(week(today()-(7 * vAntalVeckor)),00)

,[Ordered qty],0))

/vAntalVeckor, Category, Colour, LagerArtikel_, SvItemdesc, [Lager/Risk]), 1))

)

Hope this helps!

Re: Conditional Total

Tue, 11 Mar 2014 20:49:22 GMT

this is correct!

Thank you

However furthering my knowledge, is it not possible to show the total row but not show the aggregated value in the total column cell for all '8' lagerartikel_.

in SAP BW this is possible but i believe in QV this is potentially not available?

Best

Brad

Re: Conditional Total

jerem1234 — Wed, 12 Mar 2014 13:04:07 GMT

So you want the total row still shown for the 8's, but not show any value? You'll have to add another "if" to your equation like:

if(dimensionality()<>1, sum(Round(Aggr(

sum(if([OrderYearWeek] >=

weekyear(today()-(7 * vAntalVeckor))&num(week(today()-(7 * vAntalVeckor)),00)

,[Ordered qty],0))

/vAntalVeckor, Category, Colour, LagerArtikel_, SvItemdesc, [Lager/Risk]), 1)) ,

if(left(maxstring(LagerArtikel_), 1) <> 8,

sum({<LagerArtikel_= {'=left(LagerArtikel_,1)<>8'}>}Round(Aggr(

sum({<LagerArtikel_= {'=left(LagerArtikel_,1)<>8'}>}if([OrderYearWeek] >=

weekyear(today()-(7 * vAntalVeckor))&num(week(today()-(7 * vAntalVeckor)),00)

,[Ordered qty],0))

/vAntalVeckor, Category, Colour, LagerArtikel_, SvItemdesc, [Lager/Risk]), 1)),

' ')

)

Here since there is more than one LagerArtikel_ for the total, you'll need to just take one of these by using maxstring. Then for the total, I just used the blank space for the 8's(you can put whatever you want there). Hopefully that is what you're asking, if not can you give an example of of what you want shown.

Hope this helps!

Re: Conditional Total

Wed, 12 Mar 2014 23:53:03 GMT

correct thank you