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: 
Not applicable

Trying to aggregate data to find the x% of total sales

Hi there,

Background: The company I work for have just purchased Qlikview and we are now attempting to write our first apps, we've had some developer and layout training but now the real work begins

Issue: The sales department want to categorise the products they sell into three categories. Cat 1 are the items that make up the top 50% of the revenue generated over the past 10 working days, Cat 2 are the next 30% and Cat 3 the last 20% . The issue I have is that I cannot work out how to get Qlikview to categorise the items (it needs to be dynamic as the user wants to be able to have the facility to pick a vendor or range of vendors and the categorisation will then change to fit the sales for those items), I've discovered the FIRSTWORKDATE function which enables me to find the correct date to base the start of my sales from (not including bank holidays but I guess I could add them manually as 'holiday dates')

If I was in Excel I could sort the data descending in value for total sales over the ten day period and then have a rolling total as the data decreased in value which I could use with an IF statement to flag the items into their relevant category. I've attached an image of what I'm trying to describe if it helps.

Can anyone help me please?

Thank you for your time

Richard

error loading image

1 Solution

Accepted Solutions
Not applicable
Author

Hi Richardho, John

An another way to do it is to use rangesum. This will enable us to categorize in one step. I have appended to your solution John. Please find it attached.

Kind Regards

Footsie

View solution in original post

4 Replies
johnw
Champion III
Champion III

Here's an example of assigning the categories the same way as you show in Excel. The problem, if it's a problem for you, is that I don't know how to make those categories "stick" outside of the chart that calculates them. There are ways to use aggr() and other functions to internally make a table within an expression and then pull results from it, but I know of no way to sort within the table generated by aggr(), and in this case sorting by the sum in descending order is critical. That might all be gibberish to you anyway. So maybe this will help, maybe it won't.

Not applicable
Author

Hi Richardho, John

An another way to do it is to use rangesum. This will enable us to categorize in one step. I have appended to your solution John. Please find it attached.

Kind Regards

Footsie

johnw
Champion III
Champion III

Ah, yeah, that's even simpler. Good solution. Smile

Not applicable
Author

Hi John,

Just need your extended support for one of our client reuiqrement.

We pasted (Hardcoded) our reuirement in the .qvw , which is extension of your above solution .

What we need here is how to bring "Rangesum" into List box or user defined field.

We need to calculate dynamically based on period as well as other selections.