topic Re: Date in QlikView

Date

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

Hi Expertise

Please find the attachment of the QVW file.

In the script Main 2

I am creating two variables ..vReportYearStart which should show me the value of 2015/01/01

and the second variable is vReportYearAccumalate should contain the value 2015/12/01

Iam doing this becuase i want to show PDReportPeriod from Dec-2015 onwards ...

but currently this is showing from Dec-2014

PLease help me in achiving this ...

Thanks in advance

Re: Date

PrashantSangle — Tue, 27 Mar 2018 05:42:44 GMT

Hi,

change your variable definition as

vReportYearAccumalate=MonthStart(AddMonths(Date#('$(vReportYearStart)'),11))

Regards

Re: Date

smilingjohn — Tue, 27 Mar 2018 05:51:22 GMT

Hi Prashanth Thanks for the reply ...

I checked the variable vReportYearAccumalate and now it has 12-01-2015

but the PDReportPeriod is showing the date from Jan-2015 as shown in the screenshot ..

Actaully i need to show The PDReportPeriod from Dec-2015 \

Which is showing when i use below variables which are not correct as per business logic ..

Let vReportYearStart = Date(YearStart(AddYears(Today(),-2))); //2015/01/01

Let vReportYearAccumalate = MonthStart(AddMonths(Date#('$(vReportYearStart)'),-1));

And i dont know this is giving the date of PD reportPeriod what i wanted as shown in the image below ...

Re: Date

PrashantSangle — Tue, 27 Mar 2018 05:59:35 GMT

Hi,

file which you have uploaded in that i found below variable definition

Let vReportYearStart = Date(YearStart(AddYears(Today(),-3))); //2015/01/01

Let vReportYearAccumalate = MonthStart(AddMonths(Date#('$(vReportYearStart)'),11)); //2015/12/01

Where vReportYearStart return 2015/01/01

and vReportYearAccumalate return //2015/12/01

So if you want from dec 15 then change vReportYearStart as

Date(YearStart(AddYears(Today(),-2)))

and in your script do highlighted modification

Department:

LOAD

Key as Key,

1 as PDOpenCount,

0 as PDCloseCount,

1 as PDRunningOpenCount,

Date(MonthStart(Created),'MMM-YYYY') as PDReportPeriod

,Jira_Severity as PDJira_Severity,

Created as PDCreated,

DefectClosed as PDClosed,

Client as PDClient,

Summary as PDSummary

RESIDENT Jira

Where [When Found] = 'Production'

AND Created > date('$(vReportYearStart)') ; // Remove =

Regards,

Re: Date

smilingjohn — Tue, 27 Mar 2018 06:10:40 GMT

Hi Prashanth ...

As per the requirement the vReportYearsStart should hold the values 2015/01/01

thats the reason i wrote Today()-3...

If i change it to Today()-2 and then

Let vReportYearAccumalate = MonthStart(AddMonths(Date#('$(vReportYearStart)'),-1))

this is giving me me the dates in PDReportPeriod from Dec-2015...

I hope i was clear to a extent to give u an idea what i want ..i hope you got it ..

Thanks

Re: Date

smilingjohn — Tue, 27 Mar 2018 06:13:30 GMT

Hi Prashanth and also it will be great if you can please explain me what are this o as 1 as and -1 as are for ?

I am trying to understand this but not getting what are they trying to do here ?

Thanks in advance

Re: Date

PrashantSangle — Tue, 27 Mar 2018 06:36:08 GMT

Hi,

see in help menu.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/addmonths.htm‌

and

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/date-time-fun…

Regards,

Re: Date

PrashantSangle — Tue, 27 Mar 2018 06:44:25 GMT

Hi,

as per your logic written script

Department:

LOAD

Key as Key,

1 as PDOpenCount,

0 as PDCloseCount,

1 as PDRunningOpenCount,

Date(MonthStart(Created),'MMM-YYYY') as PDReportPeriod,

,Jira_Severity as PDJira_Severity,

Created as PDCreated,

DefectClosed as PDClosed,

Client as PDClient,

Summary as PDSummary

RESIDENT Jira

Where [When Found] = 'Production'

AND Created > date('$(vReportYearStart)') ;

You are taking PDReportPeriod from above table where Created >= date('$(vReportYearStart)')

Here you are taking created date > vReportYearStart

if your vReportYearStart is 1 jan 2015 then you will get created date greater equal to than 1 jan 2015 in PDReportPeriod.

and because this condition you are getting value from Jan 2015 in your listbox.

Hope you got what was your issue.

Regards,