Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Partner
Partner

Excel formula

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.

Tags (3)
7 Replies
Not applicable

Re: Excel formula

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    

Partner
Partner

Re: Excel formula

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

Not applicable

Re: Excel formula

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

daveamz01
Contributor III

Re: Excel formula

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

Partner
Partner

Re: Excel formula

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

Partner
Partner

Re: Excel formula

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

Not applicable

Re: Excel formula

Hi All,

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

use function to compute value in scripting.