problem with calculating months difference

[Anonymous]

Hi all,

QV calculated the months difference during the load job and it worked, but both dates were in "columns" => "Startdate" and "Enddate".

I've used the method from the link: Calculating Months difference between two dates

But now I have to change the startdate ... now the startdate is read from the filename:

($(MonthDiff((Date(Date#(MakeDate(mid(FileName(),24,4) ,mid(FileName(),29,2) ,mid(FileName(),32,2)),'DD.MM.YYYY'))), Enddate))) AS MonthsDifference,

Starting the script the following error message can be read on the screen:

Error in expression:

Mid takes 2-3 parameters

How can I fix it? The old method with startdate (in a column) won't be available for me in the future.

Many thanks in advance 🙂

[prieper]

Can you post an example?

Is the Filename correct?

Not quite clear, what you wish to achive with the preceding Date(Date#(-combination.

Did you try to check only the inner expression?

MakeDate(mid(FileName(),24,4) ,mid(FileName(),29,2) ,mid(FileName(),32,2))

Does it result in a date?

[Anonymous]

yes, the result is a date. For "reports" in QV the result is ok. I will post an example within the next two hours.

[Anonymous]

LOAD MakeDate(mid(FileName(),24,4) ,mid(FileName(),29,2) ,mid(FileName(),32,2)) AS [Report Date],  //Date(Date#(MakeDate(mid(FileName(),24,4) ,mid(FileName(),29,2) ,mid(FileName(),32,2)),'DD.MM.YYYY')) AS [Report Date],

MakeDate(mid(FileName(),24,4) ,mid(FileName(),29,2) ,mid(FileName(),32,2)) as %KEY_Kalender_Datum,   // Date(Date#(MakeDate(mid(FileName(),24,4) ,mid(FileName(),29,2) ,mid(FileName(),32,2)),'DD.MM.YYYY')) as %KEY_Kalender_Datum,

If (num(mid(FileName(),32,2))='20', 'yes', 'no') as Report.Monthly,

Topic,

     [Potential Customer],

     [Account Number (Potential Customer) (Account)],

     Status,

     [Est. Close Date] as Report.Close_Date,

     YearName([Est. Close Date]) as Report.Close_Year,

     [Est. Close Date] as %KEY_Kalender_Datum2,

     Owner,

     [Territory (Owning User) (User)],

     [Est. Revenue],

     [Est. Revenue (Base)],

     [Weighted Revenue],

     [Weighted Revenue (Base)],

     Probability,

     [Pipeline Phase],

     Rating,

     [Account Category (Account) (Account)],

     [Industry (Account) (Account)],

     If ( Status = ('Won') , [Est. Revenue (Base)] , if ( Status = ('Lost'), [Est. Revenue (Base)], [Weighted Revenue (Base)] )) as Reporting.Revenue,

     [Opportunity ID],

     [Actual Close Date],

     [Actual Revenue],

     [Actual Revenue (Base)],

     //($(MonthDiff(MakeDate(mid(FileName(),24,4) ,mid(FileName(),29,2) ,mid(FileName(),32,2)), [Est. Close Date]))) AS MonthsDifference,   //($(MonthDiff([Actual Close Date], [Est. Close Date]))) AS MonthsDifference,

     //if( '0' <= ($(MonthDiff([Report Date], [Est. Close Date]))) , if( ($(MonthDiff([Report Date], [Est. Close Date]))) < '19', 'relevant' , 'nrelevant') , 'nrelevant') as Reporting.Pipeline18months,

     //if( [Report Date] <= [Est. Close Date] , 'relevant' , 'nrelevant') as Reporting.Pipeline18months,

     mid(FileName(),24,4) AS Report.Year,

     mid(FileName(),29,2) AS Report.month,

     mid(FileName(),32,2) AS Report.day,

     MakeDate(mid(FileName(),24,4) ,mid(FileName(),29,2) ,mid(FileName(),32,2)) as Report_test.Date,

     'All Opportunities' as Folder

FROM

[\\qlikview\QlikView\ExportDaten\CRM\HD - All Opportunities 201*.xlsx]

(ooxml, embedded labels, table is [HD - All Opportunities]);