How to have a Config file ( not sure about this term )

[hillrunner]

Hello,

I would like to have a config file which have data which is able to overwritten the values of calculated field.

I am not sure if I explain it well but for example if i have  a graph or a table which have all of the informations about employees and in this graph there is a calculated field for example  the total commission he has won.

It is a calculated column which use other fields in the database to do a multiplication.

And i would like a config file ( not sure about the term ) which also have the column ' commision ' with a fix value and this is going to replace the value in the calculated column of the graph.

If you have ideas or related posts i am taker 🙂

thanks

[mfchmielowski]

Hi.

For what i understand. You want to on the one hand have the calculated commision for a salesman but on the other hand overwrited commision for this salesman.

Overwriting will depend on how the datamodel looks like. Basic commision systems are based by % of price by product or fixed price for product. If the datamodel will looks like:

CommisionBaseTable:
load * , year(salesdate)&'-'&num(month(salesdate),'00') as salesdateYM inline [
	salesman, salesdate, product, quantity, unitPrice, fixedUnitProductCommision
	John Doe, 2021-01-01, hotdog, 10, 1, 0.1
	John Doe, 2021-01-01, burger, 10, 1, 0.1
	John Doe, 2021-02-01, burger, 10, 1, 0.1
];

By that rule the quantity*unitPrice*fixedUnitProductCommision and thats calculated value in chart/graph.
And now you want to replace this calculated value with other value.

If your results will be presented in year-month based dimension you will have to create a key column in the data model and the code will be like:

CommisionBaseTable:
load *
	, salesman &'|'& salesdateYM as commissionOverwriteKey
;
load *, year(salesdate)&'-'&num(month(salesdate),'00') as salesdateYM inline [
	salesman, salesdate, product, quantity, unitPrice, fixedUnitProductCommision
	John Doe, 2021-01-01, hotdog, 10, 1, 0.1
	John Doe, 2021-01-01, burger, 10, 1, 0.1
	John Doe, 2021-02-01, burger, 10, 1, 0.1
];

Now it's the time for OverwriteConfigTable.

OverwriteConfigTable:
load * inline [
	commissionOverwriteKey, commissionOverwriteValue
	John Doe|2021-01, 10
];

For presentation i've used straigth table with

dimmensions: salesdateYM , salesman, product
expresions: 

sum(unitPrice*quantity)

labeled sales

if(
	sum(commissionOverwriteValue)>0
		, sum(commissionOverwriteValue)
		, sum(unitPrice*quantity*fixedUnitProductCommision)
)

labeled commision

Have i understand yours intensions correctly ?
And most of all 😉 Will this fit ?

Cheers and have a nice weekend.

[hillrunner]

thanks a lot for response, i am reading and try to understand it and i will come back 🙂 !