Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Too complicated for QlikView?

Hello

I've been asked whether QV can create reports that are like spreadsheets. Can I produce tables in which almost every cell is calculated differently?

So as an example, see the attached spreadsheet. This shows actual figures for prior year, and actual figures for the first three months of this year. Then the remaining months are shown which contain forecasts. Each row is calculated differently and needs to be calculated in the application because the users need to be able to adjust the forecasts by changing some of the parameters in the formulas via input boxes or sliders.

I've had a play and tried to create this, but I don't think the standard pivot tables are flexible enough. So the answer might simply be "QlikView is not the right tool for this kind of report". But before I say that I wondered what your opinions were.

Thanks.

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

It does sound like your users would be best served by a spreadsheet for at least the entry of the formulas.  Since you think the reporting would also be best in a spreadsheet, and you're closest to the requirement, I say go for it.  Spreadsheets are great, and QlikView is not a spreadsheet.

View solution in original post

10 Replies
Not applicable
Author

Since I do not have the csv file required for the formula for the last part (staff costs) I was unable to do the calculation for that particular part.  However, I believe the attached somewhat solves the problem you were setting out to do in QV. 

I also am not quite sure about the utility costs calculation as I tried implementing the formula of prioryear/12 + (.15 * prioryear) but the numbers weren't coming out correctly.

Hope the attached helps!

Not applicable
Author

The actual calculations are only an example. My question was more about how can you create different calcuations on each row of a table.

I will have a look at your attachment when I'm back in the office on Monday.

Many thanks!

johnw
Champion III
Champion III

As you suspected, the simple answer is "QlikView is not the right tool for this kind of report."

Mind you, it CAN do what you describe (though I didn't look at your actual spreadsheet).  It's just not easy on the developer.  See the attached example for one approach.  It could be cleaned up by using a real field instead of a valuelist() or valueloop(), though.

Not applicable
Author

Ah, I see what you have done. Putting all the calculations in the script is a good idea. The solution I need will allow the users to edit these formulas too, which they can't do in the script, so I need something slightly different.

Thanks for your help.

Not applicable
Author

I think you are right John.

This method might work for small tables, but the report I need to create has about 50 rows, all calculated differently. Building that into one expression will be a nightmare.

I'm going to advise that this is not the right tool.

Thanks for your help.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi James,

There is usually a way, but it is not always easy.  You could have a spreadsheet of expressions that contain the code you wish to have calculated.  These expressions could be identified with column and row numbers.  A data island containing a row for each row of your dynamic table would be the dimension and then you would need a separate expression for each column.  Each expression would then need to grab the right peice of code that was loaded from your spreadsheet based on the row and column.

It's would not be all that straight forward, but should be possible.  Other tools may do it in a cleaner fashion - but by doing it in QlikView you have all the advantages of associative search built in.

It may be possible to get someone in for a day to help you explore this further. 

- Steve

johnw
Champion III
Champion III

I'd mostly agree with Steve on how to approach this in QlikView.  See the attached for one way to load in expressions and then evaluate them in a chart.  It handles it by generating a variable for the chart expression based on the calculations in the "spreadsheet" (in this case, an inline load).  Building the expression then isn't a nightmare because the script builds it for you, though expanding the script to two dimensions might cause you a few fits if you're not used to this sort of thing.  My first attempt would be to just generate a single huge expression based on row and column rather than having a separate expression for each column, and then build the chart as a pivot table with the "Column" dimension at the top.

Not applicable
Author

Thanks John

That's really useful, I've been looking for a way to do that for a while!

The other issue is that the users want to be able to alter these formulas too, and then send the report out to other people so that they can tweak them further.

I still think they are best served by a spreadsheet.

James

johnw
Champion III
Champion III

It does sound like your users would be best served by a spreadsheet for at least the entry of the formulas.  Since you think the reporting would also be best in a spreadsheet, and you're closest to the requirement, I say go for it.  Spreadsheets are great, and QlikView is not a spreadsheet.