Circular reference caused by common date field and productid. How to fix?

[Anonymous]

I am trying to build a dashboard that includes sales and salary information for teachers in several locations, who teach various courses (ProductId).

I want to analyse the teachers' salary costs (SalaryMonthlyCost) with regards to revenue generated from courses they teach (TotalSoldIncVAT) in relation to ProductId, locationId and other parameters.

As a result i have a circular reference that I don't know how to resolve because both "naughty" fields are truely linked:

I need the date field in common to be able to compare sales with salary costs across time.

I need the product id to be in common in order to compare costs vs. revenue per product id.

Anyway around this?

[rupamjyotidas]

Build a common Date Table. also advice you to make one fact table,like sales and salary in on Fact Table by Concatenation.

Thanks

Rupam

[Anonymous]

Thanks Rupam. I am afraid I am too new to understand your answer .

Can you please advise what a common date table is? a table that only includes dates? can you share an example?

If i create such a table and associate the salary dates and the sales dates with it, should it solve the circular reference? they will both still be associated to each other by the date via a third table.

What do you mean by concatenating sales and salary into a fact table? should i join both tables together? can i do it in the script as i would like to keep the source files as they are. If so, could you please point me in the right direction for creating the appropriate syntax?

sorry for all the questions and thanks for your help!

[rupamjyotidas]

I would suggest you to look at blogs in Qlik Design Blogs

Some of them are

Why You sometimes should Load a Master Table several times

Creating A Master Calendar

QlikView Resource Library