Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm using below calendar in preparing a balance sheet in QLIKSENSE.
Calander:
load
date(CAL_END_DATE) as CAL_END_DATE,
YEAR(ADDMONTHS(CAL_END_DATE,-3)) AS Year,
Date(YearEnd(CAL_END_DATE, 0, 4)) as FiscalDate,
'Q' & CEIL(MONTH(AddMonths(CAL_END_DATE,-3))/3) AS Quarter,
Month(CAL_END_DATE) as Month
Resident Table;
I'm using below two set analysis to display values for the
01. Selected period and
02. Beginning of current financial year as below.
01. CURRENT YEAR
Sum({$<FiscalDate=,CAL_END_DATE={'=Date(FiscalDate)'}>}YTD)
02.YEAR BEGINNING
Beginning of current financial year ( OR end of last financial year)
Sum({$<FiscalDate=,CAL_END_DATE={'=Date(AddYears(FiscalDate,-1))'}>}YTD)
My problem is I'm getting the SAME value that I get for the current year for the year beginning as well.
Any expert advice please.
Thanks
Senarath
Hi Senarath,
as I see is your FiscalDate allready a Date. So you don't need to make It a Date again.
So you can do Sum({$<FiscalDate=,CAL_END_DATE={'Year(FiscalDate)+1'>}YTD)
I hope this will help you.
KR
Clemens
Hi Clemens,
Yes correct but I still get the correct answer. Point No.01 is al right and it is working. My problem is I'm getting the same answer for the point number 02 above which should not be the case.
thaks
Okay, but the syntax is correct?
Maybe its work with something like this:
Sum({$<FiscalDate=,CAL_END_DATE={"$(=Date(AddYears(FiscalDate,-1)))"}>}YTD)
Try this..
Sum({<FiscalDate = CAL_END_DATE={'$(=Date(YearStart(FiscalDate)))'}>}YTD)
Sorry Guys that also do not work.....
Is it possible that you can share a qvf file?
So I can check it direktly in the file.
Did you check the date format in script ?
Provide your sample data
01. CURRENT YEAR
Sum({$<FiscalDate=,CAL_END_DATE={"$(=Date(FiscalDate))"}>}YTD)
02.YEAR BEGINNING
Beginning of current financial year ( OR end of last financial year)
Sum({$<FiscalDate=,CAL_END_DATE={"$(=Date(AddYears(FiscalDate,-1)),'YOURDATEFORMAT')"}>}YTD)
Read this thread
Try like this
LOAD date(CAL_END_DATE) as CAL_END_DATE,
YEAR(ADDMONTHS(CAL_END_DATE,-3)) AS Year,
Date(Floor(YearEnd(CAL_END_DATE, 0, 4))) as FiscalDate,
'Q' & CEIL(MONTH(AddMonths(CAL_END_DATE,-3))/3) AS Quarter,
Month(CAL_END_DATE) as Month
Resident Table;
and then this:
01. CURRENT YEAR
Sum({$<FiscalDate, CAL_END_DATE = {"$(=Date(Max(FiscalDate)))"}>}YTD)
02.YEAR BEGINNING
Sum({$<FiscalDate, CAL_END_DATE = {"$(=Date(AddYears(Max(FiscalDate), -1)))"}>}YTD)