Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, guys!
Very intersting task for Qlikview scripting.
I have data set for ''project code'', "hours", "rate" and "realization" rate.
The income is recognized when hours x rate x realization.
However, the realization changes over time.
When the realization changes the income is recongized by last available realization.
The formula should aggregate the value like "hours" ((1) x "rate" (1) + "hours" (2) + "rate" (2)) x realization (2). something like this.
I include the input data. Please propose any script.
Kind regards,
Ruslan
Data:
LOAD Month,
num(month(Date#(Month,'MMMM'))) as MonthNum,
Employee,
[Job code],
HRS,
Rate,
HRS*Rate as HRSRate
FROM
[data (1).xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join(Data)
LOAD Month,
[Job code],
Realization/100 as Realization
FROM
[data (1).xlsx]
(ooxml, embedded labels, table is Sheet2);
Final:
LOAD *,
HRS_Rate*Realization as Results;
LOAD *,
if([Job code]<>Previous([Job code]),HRSRate,rangesum(HRSRate,Peek('HRS_Rate'))) as HRS_Rate
Resident Data
Order by [Job code],MonthNum;
DROP Table Data;
DROP Fields HRSRate,HRS_Rate;
The logic is correct.
However, when I select July, the total amount is shown for June+July, despite the July amounts should be shown only. The aggregated value should be shown as total if I selected both months.
Could you please fix it?
Kind regards,
Ruslan
Finally it worked out with this example. Unfortunately, when I transfer to existing data model not all job codes are recalculated.
let me share to you my qvw file?
Kind regards,
Ruslan
What should be the Amount when you select July and when you select June & july?
When I select July, the formula should use realization for July. When I select June, the formula should select the realization for June, respectively. But when no selections made or selections of both months, the formula should use the latest available realization rate...
Please provide the expected output in all the scenario. I am still confused with requirement
The scenario 1:
Select June:
job code | Month | HRS | Rate | Realization | Result |
---|---|---|---|---|---|
123 | June | 5 | 10 000 | 30% | 15 000 |
The same results for other job codes as well.
Select July:
Job code | Month | Hrs | Rate | Realization | Result |
---|---|---|---|---|---|
123 | Julty | 8 | 10 000 | 40% | 32 000 |
Select both Month or Select nothing
job code | Month | Hrs | Rate | Realization | Results |
---|---|---|---|---|---|
123 | - | 13 | 10 000 | 40% | 52 000 |
Hope it's clear.
Please see the script maybe due to the error in scrip I am unable to apply the formula:
Realizations:
LOAD
Realisation,
[Job code]&'_'&Period as KEY
FROM [Data\Realizations by month.xlsx] (ooxml, embedded labels, table is Sheet1);
MapRealization:
Mapping
LOAD Distinct KEY, Realisation
Resident Realizations;
Advisory:
LOAD BU,
[Client name],
[Job code],
[Eng. name],
[Work dt],
Period,
Month(Period) as Date,
QuarterStart(Period) as Quarter,
Hrs,
Tr.Reference,
Tr.Type,
Descript,
Ref.,
Entity,
[Tr type],
[Journ Type],
[Empl code],
[Empl name],
[Empl office],
[Empl dep],
[Eng CC],
EngMan,
[Job code]&'_'&Period as KEY,
ApplyMap('MapRealization', [Job code]&'_'&Period,40) as AdjRealization
FROM [Data\July'17 ch hours details_kz _emc.xls] (biff, embedded labels, table is Advisory$);
Inner Join (Advisory)
MC_Employees:
LOAD [Empl name],
Grade,
City,
Group,
Month(Period) as Date
FROM [Data\MC employee list.xlsx] (ooxml, embedded labels, table is Sheet1);
MC_Rates:
LOAD Position as Grade,
[Rate (KZT)]
FROM [Data\MC rates in KZT.xlsx] (ooxml, embedded labels, table is Sheet1);
Budget:
LOAD Month(Date) as Date, Group, Amount as BudgetAmount
FROM Data\Budget.xlsx (ooxml, embedded labels, table is Sheet1);