topic Re: need help with relative expression in QlikView

need help with relative expression

Anonymous — Tue, 01 May 2012 14:36:23 GMT

Hi ,

i need to get relative percentage for the attached screenshot. for values feild, i'm using relative option in straight table but , i need my chart to be in pivot table

for values column- i'm using expression if(sum(sales)>0,sum(sales))

how to write expression to get relative percenatge and it should be aplicable for minichart next to it.

thankyou very much

have a good day

need help with relative expression

Anonymous — Tue, 01 May 2012 14:37:34 GMT

forgot to mention that i have used 2 dimension for that staright table

Re: need help with relative expression

Anonymous — Tue, 01 May 2012 16:00:32 GMT

Am I not clear with my question

Re: need help with relative expression

Anonymous — Wed, 02 May 2012 13:35:55 GMT

any leads ,

as i'm new to qlikveiw i couldn't able to workout this expression any inputs or suggestion would be very helpful

need help with relative expression

SunilChauhan — Wed, 02 May 2012 14:19:47 GMT

relative % can be calculated using

sum(sales)/sum(total <keyfield>sales) for sales where keyfield is primary key

sum(Values)/sum(total <keyfield>Values) for Values where keyfield is primary key

can you attached sample file???

Re: need help with relative expression

Anonymous — Wed, 02 May 2012 14:55:20 GMT

sry fo rlate reply & thank you very much for the reply..

i have some negative values in my feild ....i need to add only the postive values and need there relative % for my bar to show up

plz look at the attached file ..the mini chart to looki selected relative i need an expression to work with it

Re: need help with relative expression

SunilChauhan — Wed, 02 May 2012 15:12:00 GMT

see the attached file

hope this helps

Re: need help with relative expression

Anonymous — Wed, 02 May 2012 15:38:56 GMT

hi sunil, i just look the worked out attachment

but i need to show only four bar which are having + ve values and i need my chart to be in pivot

so is any other expr to work out

can plz look into it and help me wth better expression

Re: need help with relative expression

IAMDV — Thu, 03 May 2012 14:40:02 GMT

Hi Srikanth,

This is what you need on Pivot Table :

IF(sum(INCREMENTAL_REV)>0,

sum(INCREMENTAL_REV)/ SUM(TOTAL

IF(

aggr(SUM(INCREMENTAL_REV), BRAND, DISCOUNT_FROM_NEW_RETAIL_BUCKET) > 0,

aggr(SUM(INCREMENTAL_REV), BRAND, DISCOUNT_FROM_NEW_RETAIL_BUCKET)

)

I haven't tested it but hopefully it should work. Let me know if you need more help.

Good luck!

Message was edited by: D V

I have tested the expression and it works fine. Here is the attachment.

Cheers - DV

www.QlikShare.com

Re: need help with relative expression

Anonymous — Thu, 03 May 2012 14:55:54 GMT

yup great DV ..the expression works very fine ... than you very much

but linear guage (bar) is not changing according to the values all the bar are the look the same

please can you look into the attached qvw

Re: need help with relative expression

IAMDV — Thu, 03 May 2012 15:07:17 GMT

Srikanth - You are welcome! I saw your attachment and you are multiplying by 100. I understand why you are doing this but it's not necessary in your case because the Min and Max values in the guage bar chart are 0 and 1 respectively. If you wanted to multiply with 100 then you need to change Max value to 100 instead of 1.

You can change the Min and Max values by selecting "Guage Settings" button.

I hope it makes sense! and I'm attaching the updated version for your reference.

Cheers - DV

http://QlikShare.com

Re: need help with relative expression

Anonymous — Thu, 03 May 2012 15:19:52 GMT

oh ya ...it's working thank you very much DV

but here is one more problem when my user selects any brand the linear guage(bars) are not static..there changing

is there a way that we make them static as they look now

sorry to trouble you DV

Re: need help with relative expression

IAMDV — Thu, 03 May 2012 15:36:19 GMT

Hi Srikanth,

You are welcome . Don't have to be sorry, the whole purpose of having this community is to seek help.

I had gut feeling that you are going to ask this question. You just to need to exclude these two fields and we need to change the expression to SET expression instead of normal expression. I'm attaching the updated version.

IF(SUM({<BRAND =, INCREMENTAL_REV =>} INCREMENTAL_REV)>0,

SUM({<BRAND =, INCREMENTAL_REV =>} INCREMENTAL_REV)/ SUM({<BRAND =, INCREMENTAL_REV =>} TOTAL

IF(

aggr(SUM({<BRAND =, INCREMENTAL_REV =>} INCREMENTAL_REV), BRAND, DISCOUNT_FROM_NEW_RETAIL_BUCKET) > 0,

aggr(SUM({<BRAND =, INCREMENTAL_REV =>} INCREMENTAL_REV), BRAND, DISCOUNT_FROM_NEW_RETAIL_BUCKET)

)

) * 100

Hopefully this should serve the purpose.

Cheers - DV

need help with relative expression

Anonymous — Thu, 03 May 2012 15:47:03 GMT

thanks DV ..

u made my work very easy strugling with the this issue from past 1 week

thank you very much realy appreciate it ..great job

Re: need help with relative expression

IAMDV — Thu, 03 May 2012 15:54:35 GMT

Srikanth,

I'm glad to know that I was helpful. It was very interesting question and I have learnt something new by answering this question. Thanks for posting this question on the other thread (Although, not sure why you deleted/removed!).

Good luck!

Cheers - DV

Sorry - Ignore my previous message, your post was not deleted/removed. I couldn't see it earlier but I can see it now. Also, I'm planning to use this post as an example to create a video tutorial on Advanced Nested Aggregations in QlikView, Is it okay to use the above QV file for demonstration purpose?

Thanks again!

need help with relative expression

Anonymous — Fri, 04 May 2012 13:01:11 GMT

sorry to say , this data is a bit sensitive, u can try a video tutorial using some mockup data

the problem is that if any one from client side came acros this then , i will loose my job

have a great day..thanks again!

Re: need help with relative expression

Mon, 25 May 2015 11:04:36 GMT

thank you