Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Pulkit_Thukral
Partner - Creator II
Partner - Creator II

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
Author

Thanks for you repli.

But Date fields are mandatory in my expressions.

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

Anonymous
Not applicable
Author

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

Pulkit_Thukral
Partner - Creator II
Partner - Creator II

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

Pulkit_Thukral
Partner - Creator II
Partner - Creator II

Hi Vidhyavadkap,

Does this completes your requirement ?

Not applicable
Author

hi Pulkit,

no it doesn't work

sunny_talwar

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
Author

Current Year = Year(Max(Date))

Current Month = month(Max(Date))

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

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