Qlik Community

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.
senarath
Not applicable

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

I hope this will help you.

KR

Clemens

senarath
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

stiffi88
Not applicable

Re: Date problem in Set Analysis - QLIK SENSE

Okay, but the syntax is correct?

Maybe its work with something like this:

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

rgvavihs
Not applicable

Re: Date problem in Set Analysis - QLIK SENSE

Try this..

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

senarath
Not applicable

Re: Date problem in Set Analysis - QLIK SENSE

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

stiffi88
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.

rgvavihs
Not applicable

Re: Date problem in Set Analysis - QLIK SENSE

Did you check the date format in script ?

Provide your sample data

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

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


Read this thread

Dates in Set Analysis

sunny_talwar
Not applicable

Re: Date problem in Set Analysis - QLIK SENSE

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)