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: 
senarath
Creator III
Creator III

Date problem in Set Analysis - QLIK SENSE

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

9 Replies
stiffi88
Partner - Contributor III
Partner - Contributor III

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

senarath
Creator III
Creator III
Author

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

stiffi88
Partner - Contributor III
Partner - Contributor III

Okay, but the syntax is correct?

Maybe its work with something like this:

Sum({$<FiscalDate=,CAL_END_DATE={"$(=Date(AddYears(FiscalDate,-1)))"}>}YTD)

Anonymous
Not applicable

Try this..

Sum({<FiscalDate = CAL_END_DATE={'$(=Date(YearStart(FiscalDate)))'}>}YTD)

senarath
Creator III
Creator III
Author

Sorry Guys that also do not work.....

stiffi88
Partner - Contributor III
Partner - Contributor III

Is it possible that you can share a qvf file?

So I can check it direktly in the file.

Anonymous
Not applicable

Did you check the date format in script ?

Provide your sample data

vinieme12
Champion III
Champion III

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

Dates in Set Analysis

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

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)