topic Re: Excel formula in QlikView

Excel formula

andrefpc — Fri, 02 Aug 2013 11:11:55 GMT

Hi everyone...I have quite a big project with qlikview, with lots of formulas and I was thinking of some way to optimize its calculation...I tought of an excel file, with the formulas in QV syntax and in QV, where I need the value to be displayed I just invoke the correct line of the excel.

Is this possible?

Thanks in advance.

Re: Excel formula

Mon, 05 Aug 2013 03:45:36 GMT

Hi andrefpc,

Could you please explain your situation with an example. It would be easier to understand whats the problem if there was a scenario.

Thanks.

Best Regards,

Ram

Re: Excel formula

andrefpc — Mon, 05 Aug 2013 08:18:30 GMT

Hi Ram, thanks for your reply! Basically I my excel spreadsheet would have 2 columns, one with the ID and one with the formula:

ID Formula

1 =sum({somekindofsetanalysis} $(amountofsales))

And in QV where I need to know the amount of sales, I'll just invoke the Excel line, based on the ID attribute, not quite sure wether this is possbile and if is the correct approach.

Regards,

Andre

Re: Excel formula

Mon, 05 Aug 2013 08:34:52 GMT

Hi Andre,

You should actually, convert a copy of the excel sheet into a .csv file. This would preserve the computed values, and the new .csv file would have the sales amount value instead of the formula you have used. I think this would be a much better approach.

Hope it helps you out!

Regards,

Ram

Re: Excel formula

daveamz — Mon, 05 Aug 2013 08:37:55 GMT

Hi Andre,

You can store your formulas into a .txt file like a variable eg.

vFormulaSales = qty*price

For txt file you will use include statement:

$(Include=ListOfFormulas.txt);

Then you can invoke formulas into your qlikview objects like =sum($(vFormulaSales)) or directly =$(vFormulaSales), depends on your formula;

Best regards,

David

Re: Excel formula

andrefpc — Mon, 05 Aug 2013 08:58:20 GMT

Ram doesn't that mean i need to change my .csv file anytime my sales amount changes?

Re: Excel formula

andrefpc — Mon, 05 Aug 2013 09:34:45 GMT

David, couldn't manage to do it that way either, made the .txt, load into qlikview but it's unable to do the calculations as 0 appear when I invoke the formula...

Best regards,

Andre

Re: Excel formula

Mon, 05 Aug 2013 09:39:50 GMT

Hi All,

you can write same formula in scripting after data attribute fetch in scripting table

use function to compute value in scripting.