How to make tables with derived values

freddean — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am trying to do what I thought to be quite simple but after trying to find the resources to answer the question myself I still haven't managed to get it.

I have a date, and an amount of money spent on that date. This money comes out of a pre-allocated amount of money. What I need to do is get a rolling sum of the money left in the pre-allocated amount on a day by day basis. If the amount is depleted I need to allocate the same amount again to the rolling sum, and count how many times in a month a re-allocation has to be done.

I've done it in Excel which was quite easy, I just had an if statement that said
IF CurrentAccBalance - DailySpend < 0 THEN field=CurrentAccBalance - DailySpend + 1,000,000 ELSE field=CurrentAccBalance - DailySpend

Example below:

Re: How to make tables with derived values

sunny_talwar — Tue, 08 Oct 2019 11:46:17 GMT

Try this

Table: LOAD * INLINE [ day, amount_spent 0, 0 1, 650000 2, 500000 3, 420000 4, 45000 5, 750000 6, 280000 7, 2000 8, 25000 ]; FinalTable: LOAD *, //RangeSum(Peek('cum_amt_spent'), amount_spent) as cum_amt_spent, If(day = 0, 1000000, If(RangeSum(Peek('Account'), -amount_spent) < 0, RangeSum(Peek('Account'), -amount_spent, 1000000), RangeSum(Peek('Account'), -amount_spent))) as Account, If(day = 0, 1, If(RangeSum(Peek('Account'), -amount_spent) < 0, RangeSum(Peek('Bucket_No'), 1), Peek('Bucket_No'))) as Bucket_No Resident Table Order By day; DROP Table Table;

topic Re: How to make tables with derived values in QlikView

How to make tables with derived values

Re: How to make tables with derived values