Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

How to convert my expression into load script ?

Hi All

I have Table on , my expression is below :-

=RangeSum(

Above(RangeSum(Above(Sum({<year, month>}[Amount]*-1), 0, RowNo()))),

-Sum(TOTAL Aggr(Sum({<year, month>}[Amount]*-1), YearMonth)))

Can some one advise me how to convert the above expression into load script ?

So that i can have the aP by month table with out expression.

Paul

6 Replies
paulyeo11
Valued Contributor II

Re: How to convert my expression into load script ?

My QVW file

gerhard_jakubec
New Contributor II

Re: How to convert my expression into load script ?

Hi, since I usually do such precalculations further up the datastream (by coding t-SQL stored procedures) i cannot help with any advice on converting your expression to QV script. But i'd STRONGLY recommend to Review your datamodel to make this hillarious clutter more manageable and comprehensible.

good luck

paulyeo11
Valued Contributor II

Re: How to convert my expression into load script ?

Hi Gerhart

Thank you for your input . I did try to clean up and try to make my data model more neat , but it is not possible now . Last month when I try to remove field name company COMPANY into company , immediately 2 days later I get call from my staff the report wrong.

Sent from my iPhone

gerhard_jakubec
New Contributor II

Re: How to convert my expression into load script ?

Before toching the load script i'd invest some time into fully understanding your requirement, chances are good that you can omit precalculations if you understand and optimize your real time kpi expression. After all as far as my understandig goes, pre- calculation / -aggregation is not exactly what qliik was designed for in the first place (real time in memory vs. OLAP).

what is your expression meant to calculate?

after some Investigation i understand that your exp.. calculates by month the total sum of "Amount" and subtracts the sum of "Amount" from all following months.--> Isn't this the same as accumulating monthly sums ?!? Try this and compare the results ("alternative calculation" has to be the Name of the new expression ! ) :

=Rangesum(Sum(Amount),Below([alternative calculation]))

Also verify if the option "Full Accumulation" may be of help in your case.

cheers

paulyeo11
Valued Contributor II

Re: How to convert my expression into load script ?

Hi Gerhard

Thank you very much for your help , i will try to figure out what you said. and try to find way to get solve. But not easy . Like

first time in QV , i encounter such a problem take me so long to solve.

Paul

paulyeo11
Valued Contributor II

Re: How to convert my expression into load script ?

Hi Gerhard

Appreciate your help

RangeSum(Above(RangeSum(Above( Why need repeat twice ?

Finally i got the solution. the expression is with out using range sum

Paul

Community Browser