Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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
Master
Master
Author

My QVW file

gerhard_jakubec
Contributor III
Contributor III

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
Master
Master
Author

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
Contributor III
Contributor III

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
Master
Master
Author

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
Master
Master
Author

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