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: 
MK9885
Master II
Master II

Values missing when doing sum

I'm sure this is related to data modelling.

trying to do sum (values) 

Ex:

I have a Field A as Sales Person

Field B as Locations. Each Location is tagged with a number for Sales Person.

John has location No as 2 but has 2 different locations, one is NY & NJ.

So when I sum it up, it combines NY & NJ, Instead of seperating sum for NY as 150 & NJ as 376.

I know this may require a sample or actual data to work on...Due to sensitivity, I cannot post it here.

But I can e-mail a sample if someone wants to help?

Been stuck on this issue since a week.

Any help is appreciated. @sunny_talwar 

Note: Expression I'm using us just Sum(Sales) Nothing fancy.

 

Labels (1)
8 Replies
NitinK7
Specialist
Specialist

Hi,

please try like

 

Sum(Aggr(Sum(Sales), Field A as Sales Person)).

MK9885
Master II
Master II
Author

Tried, doesn't work

Also tried

Aggr(Sum(Sales), FieldA)

Values get combined, I'm thinking my data model is having issues.

To seperate values, I used ApplyMap from main Sales table, linking it to Fact table.

So when it gets mapped to Fact, values get messed up.

Reason: Fact table do not have those missing sales person- so thats not showing. And whatever sales people are there, they are combined together to show as single location.

Or Single location should have multiple sales people but it is showing only 1.

vamsee
Specialist
Specialist

One suggestion, if you have both Sales Person & Location in one table, try storing your data into a temporary qvds and see if the data looks okay. You can also try grouping by Sales Person and Location, at the script level and check data.

How about Scramble and Reduce your data and posting the file. 

MK9885
Master II
Master II
Author

Doesn't work.

Used Grouping to join Fact table...

Thanks

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Scrumble the data and upload the sample.

https://www.youtube.com/watch?v=TNbTn-cRTIQ

sunny_talwar

Is your data coming from a single table or multiple tables? I am guessing that your joins (explicit or implicit) might not be correct which is why you see the data like this. For example, if you have sales by sales person in one table, but this table either doesn't have location information or doesn't join to another table where you have sales person and location information, there will be an issue when you try to display Sum(Sales) by salesperson and location. Sales doesn't have any idea about location. All it knows is that it is associated with a certain salesperson. But this is just a thought. It is difficult to know without seeing what you have.

MK9885
Master II
Master II
Author

Sales Person & Location are coming from same table.

Note: Sales person & location is just an example wordings..

In total I've 4 tables & 1 Master Calndar.

1st table has Sales Person, Location & their Location No's

With first table I'm creating an apply map to map my 2nd table (Net), based on Location No's to bring in Sales Person & Location. Its from excel file.. sole purpose of this table is to link locations, sales people, CLM, Location No's to actual data coming from DB.

Here I'm taking Sum(Sales Or Net) with regards to apply map from 1st table.

For 3rd table I've account No's & another unique field as CLM. This CLM is also in 1st table. Based on that, I'm using apply map to bring in Sales Person, Location & Location No's. 

These 2 fields will be used to link to 2nd table based on Sales date from 1st table & Account Date from 3rd table. Dropping these 3 fields from 3rd field to avoid cirsular ref.

With 4th table, its same again, apply map to 3 fields based but this time not on Location No's, only on Sales Person & Location Name.

Linking 4th table to 2nd table based on Sales, Location Name & Dates.

And then 5th is Calendar, getting dates from 3rd table, Accounts.

I know this may not give complete picture of what I'm doing until you see actual script or load it & see.

Please let me know if you need sample app?

I do not want to upload the app here, also wanted to do w/o scrambling the data. So I can explain better..

Thanks.

Brett_Bleess
Former Employee
Former Employee

As others have already requested, a sample app would be very helpful, you can reduce the data and/or use the Scrambling tab in the Document Properties of the app to scramble any sensitive field data before posting.  This way folks can check your data model and expressions to be sure everything is in sync.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.