Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

adrianbuzer
Contributor II

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
MVP & Luminary
MVP & Luminary

Re: Splitting values per period into equal week values

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
6 Replies

Re: Splitting values per period into equal week values

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

adrianbuzer
Contributor II

Re: Splitting values per period into equal week values

Hi

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

Add a line as below

GLPN as Period

MVP & Luminary
MVP & Luminary

Re: Splitting values per period into equal week values

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
adrianbuzer
Contributor II

Re: Splitting values per period into equal week values

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?

Re: Splitting values per period into equal week values

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;



MVP & Luminary
MVP & Luminary

Re: Splitting values per period into equal week values

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
Community Browser