topic Re: problem with calculating months difference in QlikView

problem with calculating months difference

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 🙂

Re: problem with calculating months difference

prieper — Tue, 07 Nov 2017 11:05:23 GMT

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?

Re: problem with calculating months difference

Anonymous — Tue, 07 Nov 2017 11:25:06 GMT

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

Re: problem with calculating months difference

Anonymous — Tue, 07 Nov 2017 13:41:53 GMT

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]);