Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Splitting values per period into equal week values

Hi

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

A

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);

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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)


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

Hi

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

Add a line as below

GLPN as Period

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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);


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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

   

PeriodWeekDateAmountWeekly Amount
1107-Jan4550
1214-Jan3550
1321-Jan050
1428-Jan12050
2504-Feb058
2611-Feb2858
2718-Feb4458
2825-Feb16058

   

Does that make sense?

settu_periasamy
Master III
Master III

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;



Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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)


talk is cheap, supply exceeds demand