Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am blocked with what seems an easy issue.
I load an Excel file that combine hours or amounts for several cost items.
I agree this is a strange set-up but that is how the client structured its data and have worked around all the way down except for a KPI and a bar chart formula.
The result look like this after loading my it in Qlikview and computing the TotalFigure, MonthlyCost, TotalCost, MonthlyPrice, TotalPrice
Cat. Expenditure | Cat | Start Date | End Date | MonthYear | NumberOfMonth | MonthlyFigure | TotalFigure | MonthlyCost | TotalCost | MonthlyPrice | TotalPrice |
1. Personnel | Engineer | 1/01/2016 | 31/12/2016 | 1/01/2016 | 12 | 50.00 | 600.00 | 6,501.83 | 78,021.93 | 6,989.46 | 83,873.58 |
1. Personnel | Expert | 1/01/2016 | 31/12/2016 | 1/02/2016 | 12 | 50.00 | 600.00 | 6,501.83 | 78,021.93 | 6,989.46 | 83,873.58 |
2. Direct Overhead | Engineer | 1/01/2016 | 31/12/2016 | 1/01/2016 | 12 | 10.00 | 120.00 | 1,300.37 | 15,604.39 | 1,397.89 | 16,774.72 |
2. Direct Overhead | Expert | 1/01/2016 | 31/12/2016 | 1/02/2016 | 12 | 10.00 | 120.00 | 1,300.37 | 15,604.39 | 1,397.89 | 16,774.72 |
3. Travel | 15/01/2017 | 16/01/2017 | 15/01/2017 | 1 | 6,000.00 | 6,000.00 | 6,000.00 | 6,000.00 | 6,579.00 | 6,579.00 | |
3. Travel | 23/03/2017 | 25/03/2017 | 23/03/2017 | 1 | 19,200.00 | 19,200.00 | 19,200.00 | 19,200.00 | 21,052.80 | 21,052.80 | |
5. Equipment | 1/04/2016 | 16/03/2017 | 1/02/2017 | 12 | 833.33 | 10,000.00 | 833.33 | 10,000.00 | 913.75 | 10,965.00 | |
5. Equipment | 1/04/2016 | 16/03/2017 | 1/03/2017 | 12 | 833.33 | 10,000.00 | 833.33 | 10,000.00 | 913.75 | 10,965.00 | |
6. Dissemination/Communication | 1/10/2016 | 31/12/2016 | 1/11/2016 | 3 | 333.33 | 1,000.00 | 333.33 | 1,000.00 | 365.50 | 1,096.50 | |
6. Dissemination/Communication | 1/10/2016 | 31/12/2016 | 1/12/2016 | 3 | 333.33 | 1,000.00 | 333.33 | 1,000.00 | 365.50 | 1,096.50 | |
7. Miscellaneous | 1/05/2016 | 31/07/2016 | 1/06/2016 | 3 | 1,000.00 | 3,000.00 | 1,000.00 | 3,000.00 | 1,096.50 | 3,289.50 | |
7. Miscellaneous | 1/05/2016 | 31/07/2016 | 1/07/2016 | 3 | 1,000.00 | 3,000.00 | 1,000.00 | 3,000.00 | 1,096.50 | 3,289.50 | |
8. Risk | 1/01/2016 | 31/12/2016 | 1/11/2016 | 12 | 1,000.00 | 12,000.00 | 1,000.00 | 12,000.00 | 1,096.50 | 13,158.00 | |
8. Risk | 1/01/2016 | 31/12/2016 | 1/12/2016 | 12 | 1,000.00 | 12,000.00 | 1,000.00 | 12,000.00 | 1,096.50 | 13,158.00 | |
4. External Services | 15/04/2016 | 28/04/2016 | 15/04/2016 | 1 | 10,000.00 | 10,000.00 | 10,000.00 | 10,000.00 | 10,965.00 | 10,965.00 | |
4. External Services | 1/07/2018 | 4/07/2018 | 1/07/2018 | 1 | 2,400.00 | 2,400.00 | 2,400.00 | 2,400.00 | 2,631.60 | 2,631.60 |
While I get want I want in a table, I can't manage to compute the sum of monthly cost in a KPI box or even in a bar chart.
The issue is that if Cat. Expenditure is 1. Personnal or 2. Direct Overhead, I need to apply an hourly rate [Hourly rate] from another table - see attached file - to transform the MonthlyFigure (which represents hours for those items) into a money amount. I need here to account for the Cat profile to select the right rate comprised in the Rate sheet.
But for all other cost items (3. Travel, 5. Equipment, ...), the MonthlyFigure is already an amount so I just need to sum up the amount.
I tried this formula but it does not work:
if([Cat. Expenditure]='1. Personnel (cat. PSS form 1)',sum(MonthlyFigure*[Hourly rate])
+
if([Cat. Expenditure]='2. Direct Overhead (cat. PSS form 9)',sum(MonthlyFigure*[Hourly rate])
+
sum(MonthlyFigure)))
Same issue in a bar chart. I do not get the right value, unless I include Cat. Expenditure as a dimension.
Can you help me solve this issue?
Million thanks Community guys 🙂
The LOAD below should read:
MapRates:
Mapping LOAD ....
Jeff Robertz wrote:
I tried to make it work in my script but this does not work, any chance you can help me fix this?
Here is the script:
MapRates:
LOAD Cat,
//[Basic hourly rate],
//[Direct overhead],
[Hourly rate]
FROM
...
Not sure if I understand (seems your setting is different from my mock up attached above).
Could you change and upload the sample QVW from above so that it closer matches your setting?
Hi Swuehl,
I am trying to share the file with you but this chat seems it does not allow me doing so.
Do you know how to do?
Or alternatively, I could send you the file in direct and post it back to the community once solved.
What do you think?
Jeff
I would prefer if you upload your sample file to the forum.
You should be able to upload a file by using the advanced editor: Either click on 'use advanced editor' in the top right corner of the editor, or try to edit an existing post of yours, this should also open the advanced editor.
In advanced editor, there is an 'attach' option on the lower right.
Stupid as it is, I can't attach the file here so I had to create a new post:
Sorry, not the cleanest way but I hope sharing a sample will help close both.