topic Re: Date Issue in QlikView

Date Issue

Mon, 06 Jun 2016 06:41:47 GMT

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

Re: Date Issue

Pulkit_Thukral — Mon, 06 Jun 2016 07:24:07 GMT

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'))"}

Re: Date Issue

Mon, 06 Jun 2016 08:55:00 GMT

Thanks for you repli.

But Date fields are mandatory in my expressions.

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

Re: Date Issue

Anonymous — Mon, 06 Jun 2016 09:09:25 GMT

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

Re: Date Issue

Pulkit_Thukral — Mon, 06 Jun 2016 09:43:51 GMT

//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),'##.#%')

Re: Date Issue

Pulkit_Thukral — Mon, 06 Jun 2016 12:00:24 GMT

Hi Vidhyavadkap,

Does this completes your requirement ?

Re: Date Issue

Mon, 06 Jun 2016 12:18:35 GMT

hi Pulkit,

no it doesn't work

Re: Date Issue

sunny_talwar — Tue, 07 Jun 2016 01:14:07 GMT

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

Re: Date Issue

Tue, 07 Jun 2016 02:08:06 GMT

Current Year = Year(Max(Date))

Current Month = month(Max(Date))

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

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