topic Re: Very Complex Calculation - need help!!! in QlikView

Very Complex Calculation - need help!!!

Tue, 09 Oct 2012 12:00:56 GMT

There’s a very complex problem that I have been trying to
solve in Qlikview and hitting a brick wall. So Please help!!!

To simplify let’s assume I have following fields:

There could be 1000 assets and data goes back to last 15
years or so. I want to calculate Ratio

User can only select one period i.e.

Qtr to March 2011

Qtr to June 2011

Qtr to Sept 2011

Qtr to Dec 2011

Qtr to March 2012

Qtr to June 2012

6 months to June 2011

Year to June 2011 (going back last 6 months)

I have tried set analysis but to no avail. My last resort is writing a macro. But I don’t know whether I can use set analysis in a macro.
Also how do I access particular rows of data in a macro? For example I can load this data into a tablebox but how do I access a particular row or value?

Re: Very Complex Calculation - need help!!!

Wed, 10 Oct 2012 03:48:05 GMT

Hi,

If the user selects "Qtr to March 2012" as the period, what do you need to see as the output chart?

I assume you need to see a straight chart, but could you pls. write what exactly it should display as result?

Regards,

http://www.google.com/analytics/

Re: Very Complex Calculation - need help!!!

Wed, 10 Oct 2012 06:55:05 GMT

I just need to calculate the numbers. No charts.

(Sent from iPhone. Please excuse brevity and spellings)

Re: Very Complex Calculation - need help!!!

hallquist_nate — Wed, 10 Oct 2012 16:13:16 GMT

I think what Bilge is asking is what you expect the output to be, and a Straight Chart is a spreadsheet-like object. I think what you need to do here is use a something like "...(Max(Field)-1) in your set analysis. Your expression would be something like...

Max(Field) / (Max(Field)-1)

I don't have the exact sytax, but here is an example of something I've used....

((Sum({$< BookedYear={$(=Max(BookedYear))}>} (ORDERED_QUANTITY*UNIT_SELLING_PRICE)))

(Sum({$< BookedYear={$(=Max(BookedYear)-1)}>} (ORDERED_QUANTITY*UNIT_SELLING_PRICE)))

I would also use the Month field to create a field that indicates what Quarter it is, so you can select Q2, and year 2012.

Good Luck...

Re: Very Complex Calculation - need help!!!

christian77 — Wed, 10 Oct 2012 16:36:15 GMT

Try top(column(2), Total 4)

That means: starting from top, column 2 row nº 4. the total modifier is to avoid reseting when changing 2nd dimension.

In your table, Year 2011, month apr, value=400

Read inter-record functions for tables. They are lots of combinations. try them and see what's best for you. They are bottom, first, last, above, below, rowno, columnno, etc.

You can do with a macro if you want to extract any value from the table.

I posted Any value, any cell, anywhere long ago but they took it away when they changed the site.

good luck.