topic Re: How to write Expressin in QlikView

How to write Expressin

wenkew186 — Tue, 21 Apr 2015 08:46:33 GMT

Hi Guys,

I have a pivot table. like attachment. 2 dim and one expression.

Column "Cluster count of Subregion" just for compare. it means how many Custer belong to the Sub region.

Expression Logic is : Sum(Value)/Sum(DayNum)

Requirement point is :

when "Cluster count of Subregion" >1, the Expression result it should be every Cluster's Sum(Value)/Sum(DayNm) add.

when "Cluster count of Subregion" =1 or 0, the Expression result is Sum(Value)/Sum(DayNum).

The background of the question:

because data source is excel file. and the Subregion data is added by every (Cluster data /DayNum) if have Cluster.

like:

Sum({<Subregion='Subregion1'>}Value) /Sum({<Subregion='Subregion1'>}DayNum)

Sum({<Cluster='Cluster1'>} Value)/Sum({<Cluster='Cluster1'>}DayNum)

+Sum({<Cluster='Cluster2'>} Value)/Sum({<Cluster='Cluster2'>}DayNum)

+Sum({<Cluster='Cluster3'>} Value)/Sum({<Cluster='Cluster3'>}DayNum)

+Sum({<Cluster='Cluster4'>} Value)/Sum({<Cluster='Cluster4'>}DayNum)

......................................

Question: How to write the Expression ?

Please help ?

thanks,

Kevin.

Re: How to write Expressin

wenkew186 — Tue, 21 Apr 2015 16:59:37 GMT

Sorry for my Sample is not clear. I re-uploading the sample.

My Expression like this:

1. Cluster is dynamic, so it should be control by variable. I can't every Subregion and ever Cluster hard code in expression.

2. the Total is null, how to let it show the Sun of the Row like straight? i have to use pivot table .

I have conceive to use "COUNT(DISTINCT {<Subregion>} Cluster)" to judgment which Subregion have more than 1 Cluster then Expression use every Cluster SUM(Amount)/sum(DayNum) add, if not, then just SUM(Amount)/sum(DayNum).

How about pick match function? and it may be need loop calculate in Expression, ! I'm not sure. Can any help me?

thank you so much!

Re: How to write Expressin

Tue, 21 Apr 2015 17:13:18 GMT

Give me sample data for excel.

while using match function to avoid the nested if conditions .

Thanks,

Srinu

Re: How to write Expressin

wenkew186 — Tue, 21 Apr 2015 17:26:06 GMT

Hi,

this sample is right.

Re: How to write Expressin

ramoncova06 — Wed, 22 Apr 2015 00:47:26 GMT

have you tried with sum(total <subregion> Amount)/sum( total <subregion> daynum)

Re: How to write Expressin

sunny_talwar — Wed, 22 Apr 2015 01:34:50 GMT

See if this is what you are looking for? PFA

Best,

Sunny

Re: How to write Expressin

wenkew186 — Wed, 22 Apr 2015 02:26:30 GMT

thank you sunindia,

yes, I hope look the Total is like this.

But it is not my only problem,

1.I need dynamic the expression. now all the hard code in here. like Subregion='A' and Cluster ='A1'. I need use variable to replace it.

2. another problem in my sample. when click a Subregion, the Dim Group not drop down and no data.

Re: How to write Expressin

wenkew186 — Wed, 22 Apr 2015 02:46:46 GMT

thanks for your response Ramon,

My expression is like this, and because I need add every Cluster Sum(Amount)/Sum(DayNum) when Subregion has Cluster. so that why I use this complex expression and nit use just Sum(Amount)/Sum(DayNum).

thanks.

Re: How to write Expressin

sunny_talwar — Wed, 22 Apr 2015 10:13:47 GMT

Check this out: (PFA)

Best,

Sunny

Re: How to write Expressin

sunny_talwar — Wed, 22 Apr 2015 15:13:27 GMT

Did you try the the application I posted for you?

Best,

Sunny

Re: How to write Expressin

ramoncova06 — Wed, 22 Apr 2015 15:22:01 GMT

you don't have to write it that way

the total <subregion> will give you sum grouped by subregion

Re: How to write Expressin

wenkew186 — Fri, 24 Apr 2015 07:21:28 GMT

Hi suninda,

yes, it is works good for my case, it's a great help for me.thank you so much.