Linking tables in Data Model

[linoyel]

Hi,

I have sales, bonuses and calendar tables.

Sales table holds agent sales data per date. It's linked to the calendar on date field.

Bonuses table holds MONTHLY agent bonuses. It has only year and month fields, but not the date.

I'm supposed to show in a table monthly sales and bonuses for agents.

How am I supposed to link bonuses table to the sales table?

Only by agent_id is not enough, I also have to link some date field in order to link it right to the calendar...

Please help

[marcus_sommer]

Have a look on this thread: Is it possible to run a macro for each row of a chart (table)

- Marcus

[linoyel]

Hi Marcus, I didn't really understand the connection...

[maxgro]

some ideas

group sales by month (if you don't need by date)

or replace month year fields in monthly bonuses with date (1st of month); in the user interface use month for the analysis of bonuses and sales

or http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

[joshabbott · Accepted solution]

It sounds like you'll have to be very careful with this data.  From your Sales table, create a key based on the month and year of the date:

autonumber([salesperson] & num(month([date field])) & '|' & year([date field])) as monthlybonuslink

Add this field to the monthly bonus table also:

autonumber([salesperson] & '|' [month] & '|' & [year]) as monthlybonuslink

You will then have a link to the table, but depending how you want to display the bonus amount, you'll need to be careful because there will be a link for every salesperson/date.  For instance, if Salesperson A had 10 orders in January 2014, and 1 bonus, if you did a sum on bonus amount, you would get bonus * 10.

[marcus_sommer]

On the from these posting you will find a load-example and a few explanations:

Data:

Load Customer, Date, Sales From Sales;

     concatenate

Load Customer, makedate(Year, Month, 1) as Date, Budget From Budget;

- Marcus

[simenkg]

Facts:

load Employee,

Date as %DateKey,

Amount,

'Sales' as FactType

from Sales.qvd (qvd);

Concatenate(Facts)

load Employee,

Makedate(Year,Month) as %DateKey,

Amount,

'Bonus' as FactType

from Bonus.qvd (qvd);

bonus would then be sum({$<FactType={'Bonus'}>}Amount)

sales would be sum({$<FactType={'Sales'}>}Amount)

[linoyel]

Hey Josh, yeah - that's the approach that worked Thanks!

I linked the sales and bonuses tables on 2 fields: agent_id and YearMonth.

In order to get YearMonth I used MakeDate() function in both tables

Regarding displaying sales data it's gonna be sum(sales) and bonus as is (not sum(bonus)).

[linoyel]

Hey Massimo,

Thanks for the answer.

I did replace month year fields in monthly bonuses with date (1st of month) but when I left it as is it didn't get me monthly bonuses if there were no sale on the 1-st of each month.

So, what I did is this making YearMonth fields in both tables using MakeDate() function.

[linoyel]

Actually sum(bonus) worked as well.

It showed just the monthly bonus of an agent and not multiplied monthly bonus, even if there were few orders in different dates by the same agent.

I needed to use sum(bonus) cause I have a set analysis expression: sum({<...>} bonus)

I don't really know why it worked but it worked.

By the way, it there a way to write a set analysis expression but without the "sum"? Something like:

{<...>} bonus