Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following challenge:
I need to have a pivot table where I calculate the amount of $ per category per phone call + fixed charge
LOAD * INLINE [
Category,Call ID, YYYY\MM,
New customer, 1, 2019\01
New customer, 2, 2019\02
New customer, 3, 2019\03
Change password, 4, 2019\03
Change password, 5, 2019\04
Invoice issues, 6, 2019\05
Invoice issues, 7, 2019\06
Invoice issues, 8, 2019\07
];
LOAD * INLINE [
Category, Price
New customer, 10
Change password,15
Invoice issues,20
];
then I calculate how much each call cost depending on a category:
Count(distinct [Call ID])*Price
how can I add extra line 'Fixed charge' under each month and calculate it in the totals like below?
120 $ per year --> 10 $ per month:
Greetings,
Polina
I did some modifications in the script to get this result:
Fixed Charge are added accordingly as your data expand to a new month.
Since there is no data for Aug, Sep, Oct etc, Fixed Charge for these months are not added into the table.
Refer qvw below for reference.
Thanks and regards,
Arthur Fong
You can just manually add those lines in the Inline Tables. Like below:
LOAD * INLINE [
Category,Call ID, YYYY\MM,
........
Fixed Charge, 1, 2019\01
Fixed Charge, 2, 2019\02
Fixed Charge, 4, 2019\03
Fixed Charge, 5, 2019\04
Fixed Charge, 6, 2019\05
Fixed Charge, 7, 2019\06
Fixed Charge, 8, 2019\07
.........
];
LOAD * INLINE [
Category, Price
New customer, 10
Change password,15
Invoice issues,20
Fixed Charge, 10
];
I did some modifications in the script to get this result:
Fixed Charge are added accordingly as your data expand to a new month.
Since there is no data for Aug, Sep, Oct etc, Fixed Charge for these months are not added into the table.
Refer qvw below for reference.
Thanks and regards,
Arthur Fong
hi,
thanks for your suggestion. I also had this in mind but then I would need to do manual work every month. the suggestion from Arthur Fong excludes any manual work .
thank you anyways 🙂
greetings,
Polina
Hi Arthur ,
brilliant solution!
it works. thanks a lot!!!
Greetings,
Polina