Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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)
Where is the period field in your table? Is Date = Period or that is something which will be needed to be calculated?
Hi
Period is in the database, I just haven't loaded it in my script.
Add a line as below
GLPN as Period
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);
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?
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;
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)