topic Re: How to convert my expression into load script ? in QlikView

How to convert my expression into load script ?

paulyeo11 — Wed, 27 Jul 2016 07:25:29 GMT

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

Re: How to convert my expression into load script ?

paulyeo11 — Wed, 27 Jul 2016 07:31:06 GMT

My QVW file

Re: How to convert my expression into load script ?

gerhard_jakubec — Fri, 29 Jul 2016 09:34:12 GMT

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

Re: How to convert my expression into load script ?

paulyeo11 — Fri, 29 Jul 2016 09:57:55 GMT

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

Re: How to convert my expression into load script ?

gerhard_jakubec — Mon, 01 Aug 2016 11:51:30 GMT

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

Re: How to convert my expression into load script ?

paulyeo11 — Mon, 01 Aug 2016 12:41:59 GMT

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

Re: How to convert my expression into load script ?

paulyeo11 — Wed, 03 Aug 2016 00:28:56 GMT

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