problem of date between two data sources

[#1]

Hello,
here I have a date problem between two data sources:
- On one side an SQL data source
- The other one an excel file

I will wish to compare from a common field (account number) amounts between these two sources of data and that based on a selected time (year, quarter, month)
and also have a cumulative power if I select in January, February, March etc. ...

here is my first column formulas (from SQL)
Sum ({} <NCOMPTE={"6*"}> EC_MNT_DEBIT-EC_MNT_CREDIT)

second column (from Excel)
Sum ({} <NCOMPTE={"6*"}> AMOUNT)

could you help me?

thank you in advance, and sorry for my ignorance.

twist

[#2]

You could create a master calendar and use the following expressions in set analysis:

DateExcel = P(DateCalendar), DateSQL = P(DateCalendar)

Any selected filter will apply to both dates.

[#3]

Thank you for the quick response but could you tell me or should I insert these expressions?

thks

twist

[#4 · Accepted solution]

First, create the calendar.

At the bottom an example...

Your filters will be the fields of this calendar (Month, Year, Quarter, etc)

When you want to make a chart/table, in your set analysis you use

{<DateExcel = P(DateCalendar), DateSQL = P(DateCalendar)>}

or maybe separately, depending in what you are doing.

The P() function returns all the possible values. In this case, you are saying that you want the Excel/SQL dates that match with the current selections of your master calendar

Hope it helps

LET varMinDate = Num('01/04/2010');

LET varMaxDate = Num(today());

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

          $(varMinDate) + rowno() - 1 AS Num,

          date($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

          $(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************

MasterCalendar:

LOAD                    TempDate AS DateCalendar,

                    Week(TempDate) AS Week,

                    Year(TempDate) AS Year,

                    Month(TempDate) AS Month,

                    Day(TempDate) AS Day,

                    Weekday(TempDate) AS WeekDay,

                    'Q' & ceil(month(TempDate) / 3) AS Quarter,

                    Date(monthstart(TempDate), 'MMM-YYYY') AS

                              MonthYear,

                    Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

                    inyeartodate(TempDate, $(vToday), 0) * -1 AS

                              CurYTDFlag,

                    inyeartodate(TempDate, $(vToday), -1) * -1 AS

                              LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

[#5]

really thank you for these tips

is that we proceed in the same way to make calculations in YTD?

[#6]

For YTD you need to specify for each date.

For instance, let's say you have a specific date selected and you want to show the YTD sales for the values corresponding to SQL

Sum({<YearSQL = {$(=max(Year))}, DateSQL = {'<= $(=max(DateCalendar))'} >} Sales)

[#7]

I created the table master calendar that 's OK

Now I have a problem linking my tables:
- The table "real" and has joined the table "budget" by field"ncompte"

if I link the table and more "real" and "budget" with the field of my master calendar, Qlikview tells me there's a loop!

how do I proceed?

thank you.