Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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'))"}
Thanks for you repli.
But Date fields are mandatory in my expressions.
Is there any work around with the expressions with "Date" fields??
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))
//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),'##.#%')
Hi Vidhyavadkap,
Does this completes your requirement ?
hi Pulkit,
no it doesn't work
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
Current Year = Year(Max(Date))
Current Month = month(Max(Date))
Previous Year = =Year(addmonths(max(Date) ,- 1))
Previous Month = Month(addmonths(max(Date) ,- 1))