Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Calculation

Hey guys

I'm tasked with creating a bonus points program for our customers and for that i need to count how often they ordered something, how often the amount of items in their order was higher than x and all that.

Tables with the necessary information i could generate but now i need to generate some kinda code that counts that for me (as in : ordering more than x items at once gives this many points, every order also gives this many points) but now I'm dumbfounded in how i can ask qliksense to calculate these kinda things for me.

Can somebody give me a nudge in the right direction?

Thanks a lot for your help

Labels (6)

• ### Variables

1 Solution

Accepted Solutions
Support

Below is some sample code to show you how to handle this kind of thing. Following the sample code are images of what the data table and the aggregates table end up looking like based on this dummy data.

1. The first thing it does is force in some dummy data. I like to use the Inline statement to hand key data like this so anyone can then copy/paste as a sample to play with. You would obviously be loading from excel table or sql or wherever your real data comes from.

2. I do something in my loading called a pre-ceding load which you can search for to understand more, but it basically says load the data from the bottom first and then do something with the values. The If expression is something that would compare your value field to some hard coded number and then create another field that can be referred to later.

3. The last step I do is create another table that is based on the DataTable (Resident Load.) Notice that it does a Group By so that aggregates are prepared for each customer. The expressions are pretty simply Count and Sum. I count how many purchases there were for each customer. I Sum up the OverAmount field. Finally I demonstrate some sample of how to do a point system. The Points system could just as easily be done in the data load just like I did for the OverAmount field. You could simply say "If (Value >= 150, 125, 100) as PointsForThisPurchase" Thus the field would be 100 points for the purchase, plus additional 25 if it was over your value. Note: IF statements can be nested as well so if you give 25 for >= 150 you could then do another if instead of just using 100 points, and say 10 points if the value is greater than 110 etc.

DataTable:
// This is a preceding load and logically follows the load below it
// so first I create the dummy data, then I have some logic to check the value
// you could do this IF statement easily in your actual LOAD script from your XLS table, SQL table etc.
If (Value >= 150, 1, 0) as OverAmount;

Customer, PurchaseDate, Value
1, '20220913', 150
1, '20221101', 100
2, '20220118', 200
3, '20220920', 160
4, '20220819', 100
4, '20220917', 80
];

// This simply creates an aggregate table showing you the Expressions you could use
// notice that I use a Group By clause so I only get 1 row per customer
AggregatedData:
Count(PurchaseDate) as NumberOfPurchases,
Sum(OverAmount) as NumberOfPurchasesOverAmount,
Count(PurchaseDate) * 100 + Sum(OverAmount) * 25 as PointsForPurchases
Resident DataTable
Group By Customer;

4 Replies
Support

Below is some sample code to show you how to handle this kind of thing. Following the sample code are images of what the data table and the aggregates table end up looking like based on this dummy data.

1. The first thing it does is force in some dummy data. I like to use the Inline statement to hand key data like this so anyone can then copy/paste as a sample to play with. You would obviously be loading from excel table or sql or wherever your real data comes from.

2. I do something in my loading called a pre-ceding load which you can search for to understand more, but it basically says load the data from the bottom first and then do something with the values. The If expression is something that would compare your value field to some hard coded number and then create another field that can be referred to later.

3. The last step I do is create another table that is based on the DataTable (Resident Load.) Notice that it does a Group By so that aggregates are prepared for each customer. The expressions are pretty simply Count and Sum. I count how many purchases there were for each customer. I Sum up the OverAmount field. Finally I demonstrate some sample of how to do a point system. The Points system could just as easily be done in the data load just like I did for the OverAmount field. You could simply say "If (Value >= 150, 125, 100) as PointsForThisPurchase" Thus the field would be 100 points for the purchase, plus additional 25 if it was over your value. Note: IF statements can be nested as well so if you give 25 for >= 150 you could then do another if instead of just using 100 points, and say 10 points if the value is greater than 110 etc.

DataTable:
// This is a preceding load and logically follows the load below it
// so first I create the dummy data, then I have some logic to check the value
// you could do this IF statement easily in your actual LOAD script from your XLS table, SQL table etc.
If (Value >= 150, 1, 0) as OverAmount;

Customer, PurchaseDate, Value
1, '20220913', 150
1, '20221101', 100
2, '20220118', 200
3, '20220920', 160
4, '20220819', 100
4, '20220917', 80
];

// This simply creates an aggregate table showing you the Expressions you could use
// notice that I use a Group By clause so I only get 1 row per customer
AggregatedData:
Count(PurchaseDate) as NumberOfPurchases,
Sum(OverAmount) as NumberOfPurchasesOverAmount,
Count(PurchaseDate) * 100 + Sum(OverAmount) * 25 as PointsForPurchases
Resident DataTable
Group By Customer;

Champion III

can you post some sample data in excel?

and the expressions you need with expected output

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Contributor II
Author

Thank you very much Mr. Ruer - this helped immensely!

Contributor II
Author

It's already solved but thank you for your time Mr. Pujari 🙂

Tags