Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hillrunner
Contributor II
Contributor II

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

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

2 Replies
mfchmielowski
Creator II
Creator II

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

 

mfchmielowski_0-1619197694476.png

 

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

 

Cheers and have a nice weekend.

hillrunner
Contributor II
Contributor II
Author

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