Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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.

 

7 Replies
Highlighted
Creator II
Creator II

Re: Values missing when doing sum

Hi,

please try like

 

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

Highlighted
Master II
Master II

Re: Values missing when doing sum

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.

Highlighted
Specialist
Specialist

Re: Values missing when doing sum

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. 

Highlighted
Master II
Master II

Re: Values missing when doing sum

Doesn't work.

Used Grouping to join Fact table...

Thanks

Highlighted
Specialist III
Specialist III

Re: Values missing when doing sum

Scrumble the data and upload the sample.

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

Highlighted

Re: Values missing when doing sum

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.

Highlighted
Master II
Master II

Re: Values missing when doing sum

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.