Solved: Re: need help with relative expression - Qlik Community

Anonymous · ‎2012-05-01

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

IAMDV · ‎2012-05-03

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

View solution in original post

Anonymous · ‎2012-05-01

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

Anonymous · ‎2012-05-01

Am I not clear with my question

Anonymous · ‎2012-05-02

any leads ,

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

SunilChauhan · ‎2012-05-02

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

or

can you attached sample file???

Sunil Chauhan

Anonymous · ‎2012-05-02

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

SunilChauhan · ‎2012-05-02

see the attached file

hope this helps

Sunil Chauhan

Anonymous · ‎2012-05-02

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

IAMDV · ‎2012-05-03

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

Anonymous · ‎2012-05-03

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