topic problem of date between two data sources in QlikView

problem of date between two data sources

Wed, 04 Apr 2012 15:10:20 GMT

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

problem of date between two data sources

Wed, 04 Apr 2012 15:22:53 GMT

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.

problem of date between two data sources

Wed, 04 Apr 2012 15:26:07 GMT

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

thks

twist

problem of date between two data sources

Wed, 04 Apr 2012 15:36:31 GMT

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;

problem of date between two data sources

Wed, 04 Apr 2012 15:40:10 GMT

really thank you for these tips

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

problem of date between two data sources

Wed, 04 Apr 2012 15:49:38 GMT

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)

Re: problem of date between two data sources

Thu, 05 Apr 2012 09:41:56 GMT

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.