topic Re: Splitting values per period into equal week values in QlikView

Splitting values per period into equal week values

Anonymous — Tue, 19 Apr 2016 15:29:56 GMT

I am loading overheads into Qlikview and I want to split the total amount per period equally across weeks. For example if the total amount of overheads in period 2 is £4,000 I want to show £1,000 per week, regardless of when the amount was actually posted. Periods 3,6,9 & 12 have 5 weeks, all the other periods are 4 weeks. This is the load script I am using, what do I need to add?

Thanks

Overheads:

load
GLAA as [Overhead Amount],
GLANI as AccountKey,
GLCO as Company,
GLMCU as [Business Unit],
GLOBJ as [Object Account],
GLSUB as [Subsidiary Account],
GLDGJ as Date

from "data.qvd"
(qvd);

Re: Splitting values per period into equal week values

sunny_talwar — Tue, 19 Apr 2016 15:32:34 GMT

Where is the period field in your table? Is Date = Period or that is something which will be needed to be calculated?

Re: Splitting values per period into equal week values

Anonymous — Tue, 19 Apr 2016 15:35:45 GMT

Period is in the database, I just haven't loaded it in my script.

Add a line as below

GLPN as Period

Re: Splitting values per period into equal week values

Gysbert_Wassenaar — Tue, 19 Apr 2016 15:46:32 GMT

Perhaps like this:

Overheads:
load
GLAA as [Overhead Amount],
GLANI as AccountKey,
GLCO as Company,
GLMCU as [Business Unit],
GLOBJ as [Object Account],
GLSUB as [Subsidiary Account],
GLDGJ as Date,
GLPN as Period,

GLAA / Pick(GLPN, 4,4,5,4,4,5,4,4,5,4,4,5) as WeeklyOverhead
from "data.qvd"
(qvd);

Re: Splitting values per period into equal week values

Anonymous — Wed, 20 Apr 2016 09:27:46 GMT

Let me explain some more with some examples. What is loaded is the Amount field, but what I want to see is the average Weekly amount (per period) so that if a selection is made in the app for week 3 it shows 50, not 0

Period	Week	Date	Amount	Weekly Amount
1	1	07-Jan	45	50
1	2	14-Jan	35	50
1	3	21-Jan	0	50
1	4	28-Jan	120	50
2	5	04-Feb	0	58
2	6	11-Feb	28	58
2	7	18-Feb	44	58
2	8	25-Feb	160	58

Does that make sense?

Re: Splitting values per period into equal week values

settu_periasamy — Wed, 20 Apr 2016 10:01:29 GMT

May be you need like this..?

T1:
LOAD * INLINE [
Period, Week, Date, Amount
1, 1, 7-Jan, 45
1, 2, 14-Jan, 35
1, 3, 21-Jan, 0
1, 4, 28-Jan, 120
2, 5, 4-Feb, 0
2, 6, 11-Feb, 28
2, 7, 18-Feb, 44
2, 8, 25-Feb, 160
];

Left Join
LOAD Period,Avg(Amount) as AvgAmount Resident T1 Group by Period;

Re: Splitting values per period into equal week values

Gysbert_Wassenaar — Wed, 20 Apr 2016 10:44:36 GMT

Yes, but I think you should do this with chart expressions and not in the script. The kind of expression you're looking for is avg({1} total <Period> Amount)