Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Date Issue

Hi all,

I face a problem in Current Year and Previous Year numbers.

Please find the attached word document for the same.

Thanks in advance

8 Replies
pulkit1611
Not applicable

Re: Date Issue

Possible way of calculating this could be (Only calculation part of set analysis written below) :

Current Year current Month = Year={"$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"},Month={"$(=DATE(FLOOR(MonthStart(max(Date))),'MMM'))"}

Previous year Current Month =

Month={"$(=date(AddMonths(DATE(FLOOR(MonthStart(max(Date))),'MMM'),-12),'MMM'))"}

Current year YTD = Year={"$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"}


Previous year YTD= Year={"$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'YYYY'))"}

Not applicable

Re: Date Issue

Thanks for you repli.

But Date fields are mandatory in my expressions.

Is there any work around with the expressions with "Date" fields??

krishnamraju513
Not applicable

Re: Date Issue

Try this .

Current Year = Year(Max(Date))

Current Month = month(Max(Date))

Previous Year = =Year(addmonths(max(Date) ,- 1))

Previous Month = Month(addmonths(max(Date) ,- 1))

pulkit1611
Not applicable

Re: Date Issue

//Not Sure if we are setting year and month restriction in expression ,then why date restriction is //also required as we are comparing full month values and not particular days.


Current Year with Current Month

=num(sum({<Date={">=$(=DATE(FLOOR(YearStart(MAX(Date))),'MM/DD/YYYY'))<=$(=DATE(FLOOR(Yearend(MAX(Date))),'MM/DD/YYYY'))"}

,FIN_MONTH_NO={"$(=DATE(FLOOR(MonthStart(MAX(Date))),'MMM'))"}>}Exit_Count)

/

((sum({<FIN_YEAR_NO={"$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"}

,FIN_MONTH_NO={"$(=DATE(FLOOR(MonthStart(MAX(Date))),'MMM'))"}>}OpeningBalance)

+

sum({<FIN_YEAR_NO={"$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"}

,FIN_MONTH_NO={"$(=DATE(FLOOR(MonthStart(MAX(Date))),'MMM'))"}>}ClosingBalance))/2),'##.#%')


Previous Year with Current Month

=num(sum({<Date={">=$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'MM/DD/YYYY'))<=$(=DATE(FLOOR(Yearend(YearStart(max(Date))-1)),'MM/DD/YYYY'))"}

,FIN_MONTH_NO={"$(=date(AddMonths(DATE(FLOOR(MonthStart(max(Call_Date))),'MMM'),-12),'MMM'))"}>}Exit_Count)

/

((sum({<FIN_YEAR_NO= {"$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'YYYY'))"}

,FIN_MONTH_NO={"$(=date(AddMonths(DATE(FLOOR(MonthStart(max(Call_Date))),'MMM'),-12),'MMM'))"}>}OpeningBalance)

+

sum({<FIN_YEAR_NO= {"$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'YYYY'))"}

,FIN_MONTH_NO={"$(=date(AddMonths(DATE(FLOOR(MonthStart(max(Call_Date))),'MMM'),-12),'MMM'))"}>}ClosingBalance))/2),'##.#%')

pulkit1611
Not applicable

Re: Date Issue

Hi Vidhyavadkap,

Does this completes your requirement ?

Not applicable

Re: Date Issue

hi Pulkit,

no it doesn't work

sunny_talwar
Not applicable

Re: Date Issue

Difficult to troubleshoot problems like these by just looking at images, would you be able to share a sample to look at?

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Not applicable

Re: Date Issue

Current Year = Year(Max(Date))

Current Month = month(Max(Date))

Previous Year = =Year(addmonths(max(Date) ,- 1))

Previous Month = Month(addmonths(max(Date) ,- 1))