# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

## Date problem in Set Analysis - QLIK SENSE

Hi,

I'm using below calendar in preparing a balance sheet in QLIKSENSE.

Calander:

date(CAL_END_DATE) as CAL_END_DATE,

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)

My problem is I'm getting the SAME value that I get for the current year for the year beginning as well.

Thanks

Senarath

9 Replies
Not applicable

## Re: Date problem in Set Analysis - QLIK SENSE

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)

KR

Clemens

Not applicable

## Re: Date problem in Set Analysis - QLIK SENSE

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

Not applicable

## Re: Date problem in Set Analysis - QLIK SENSE

Okay, but the syntax is correct?

Maybe its work with something like this:

Not applicable

## Re: Date problem in Set Analysis - QLIK SENSE

Try this..

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

Not applicable

## Re: Date problem in Set Analysis - QLIK SENSE

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

## Re: Date problem in Set Analysis - QLIK SENSE

Is it possible that you can share a qvf file?

So I can check it direktly in the file.

Not applicable

## Re: Date problem in Set Analysis - QLIK SENSE

Did you check the date format in script ?

Provide your sample data

Not applicable

## Re: Date problem in Set Analysis - QLIK SENSE

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)

Dates in Set Analysis

Not applicable

## Re: Date problem in Set Analysis - QLIK SENSE

Try like this

LOAD date(CAL_END_DATE) as CAL_END_DATE,

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)

Community Browser