topic Re: How to develop complex excel calculation into qlikview in QlikView

How to develop complex excel calculation into qlikview

Tue, 28 May 2013 13:54:37 GMT

Hi,

Am having an excel data with some complex calculation, want to develop those into qlikview, but couldn't figure out how to do it.

In the attached excel, I upload into qlikview the 'DATA' sheet and try to build reports as per 'Triangles' sheet. From the 'Triangles' sheet am able to build the A and B report. But am not able to make C and D report. Please assist in building these report.

1. C report is calculated based on B - you can see the formula in excel. This , I couldn't do.

2. D report is same as B, except the grayed cells in D which are calculated based on value from C report and values from D report- you can see the formula in excel. This also seems to be difficult.

thanks in advance

Lax

Re: How to develop complex excel calculation into qlikview

Mon, 03 Jun 2013 11:55:23 GMT

Hi All,

Is the problem complex to understand or complex to achieve in qlikview ? is there any workaround for this ?

Thanks

Lax

Re: How to develop complex excel calculation into qlikview

flipside — Mon, 03 Jun 2013 12:41:23 GMT

Hi Lax,

To calculate the factors, you can use expressions as follows (as you need to reference them in table D, then perhaps variables are the best option to store the expression results) ...

Factor0 =

num(count({<[LAG quarter]={0,1},[Date declaratopn from now]-={0}>} [Claim Date])

/count({<[LAG quarter]={0}, [Date declaratopn from now]-={0}>} [Claim Date]),'0%')

Factor1=

num(count({<[LAG quarter]={0,1,2},[Date declaratopn from now]-={0,1}>} [Claim Date])

/count({<[LAG quarter]={0,1}, [Date declaratopn from now]-={0,1}>} [Claim Date]),'0%')

Factor2=

num(count({<[LAG quarter]={0,1,2,3},[Date declaratopn from now]-={0,1,2}>} [Claim Date])

/count({<[LAG quarter]={0,1,2}, [Date declaratopn from now]-={0,1,2}>} [Claim Date]),'0%')

... and so on, building up the values accordingly.

You can build a table of these variables by using ValueLoop(0,7) which will create a synthetic dimension. Using a straight table chart, use ValueLoop(0,7) as your dimension, and pick(ValueLoop(0,7)+1,Factor0,Factor1,Factor2 ...) as your expression. If you set the table to display horizontally and hide the totals and dimension columns, you are close to your excel sheet.

Table D is a bit more trickier, looks like you need some if..then..else logic in there.

flipside