topic Re: Expression to get partial sum based on logic in straight table in QlikView

Expression to get partial sum based on logic in straight table

sarathi_pm — Thu, 02 Apr 2015 08:49:27 GMT

Hi,

I have a straight table for which I need to calculate the rate based on the below logic. Can anyone help in getting me the correct expression.

Denominator Logic - Based on the Brand

If Brand = " A" then denominator should be Sum(D+E+F) for that particular group

If Brand = " B" then denominator should be Sum(D+E+G) for that particular group

If Brand = " C" then denominator should be Sum(D+E+H) for that particular group

For the Rest of the Brand it should be the same as sales

Rate = Sale/Denominator

Straight Table

Group	Sales	Brand	Denominator	Rate
Group A	10	A	65	0.15
Group A	10	B	75	0.13
Group A	20	C	75	0.27
Group A	15	D	15	1
Group A	30	E	30	1
Group A	20	F	20	1
Group A	30	G	30	1
Group A	30	H	30	1

Group B	10	A	90	0.11
Group B	30	B	80	0.38
Group B	20	C	90	0.22
Group B	50	D	50	1
Group B	10	E	10	1
Group B	30	F	30	1
Group B	20	G	20	1
Group B	30	H	30	1

Please find the attached excel file for reference.

Thanks

Sarathi

Re: Expression to get partial sum based on logic in straight table

MK_QSL — Thu, 02 Apr 2015 09:12:43 GMT

For Rate, use below expression..

File enclosed for your reference...

SUM(Sales)/(IF(Brand = 'A', SUM(TOTAL <Group> {<Brand = {'D','E','F'}>}Sales),

IF(Brand = 'B', SUM(TOTAL <Group> {<Brand = {'D','E','G'}>}Sales),

IF(Brand = 'C', SUM(TOTAL <Group> {<Brand = {'D','E','H'}>}Sales),SUM(Sales)))))

Re: Expression to get partial sum based on logic in straight table

rubenmarin — Thu, 02 Apr 2015 09:14:47 GMT

Hi Sarathi,

Denominator expression:

If(Brand='A', Sum(TOTAL <Group> {<Brand={'D','E','F'}>} Sales),

If(Brand='B', Sum(TOTAL <Group> {<Brand={'D','E','G'}>} Sales),

If(Brand='C', Sum(TOTAL <Group> {<Brand={'D','E','H'}>} Sales), Sum(Sales))))

Rate Expression:

Sum(Sales)/Denominator

Re: Expression to get partial sum based on logic in straight table

sarathi_pm — Thu, 02 Apr 2015 09:49:50 GMT

Thank you Manish. Will try this and will let you know

Re: Expression to get partial sum based on logic in straight table

sarathi_pm — Thu, 02 Apr 2015 09:50:20 GMT

Thanks Ruben. Will try these expression and will let you know

Re: Expression to get partial sum based on logic in straight table

imhappiee — Wed, 08 Apr 2015 06:28:07 GMT

Thanks sarathi for posting this question,

guys thanks for the quick help

i have other questions which are associated with this and are listed as following

- how to include this in set analysis having other conditions like

sum({$<Flag={'H'},FiscalYear={"$(=vMaxYear-1)"},FiscalQuarter={'Q1'}>....)

is there any other way with out using if condition? like dual match r so, if so please post your ideas and help me.

Kind Regards,

Anand.

Re: Expression to get partial sum based on logic in straight table

imhappiee — Wed, 08 Apr 2015 08:43:35 GMT

In addition to that can we map this calculation through inline

Mapping load:

load * Inline[

Brand, Associate_brands

A, {'D','E','F'}

B,{'D','E','G'}.....

and use this in expression by applymap in straight chart or so.. any possibilities.

If my ideas are weird , please ignore , just trying out the possibiilties to approach with an optimised solution.