Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Issue with YTD value

Hello everyone,

I face the following problem in my report.

Current Year (2016) is the Financial Year (2015-2016)

  I need to do Current Year vs Last Year YTD Comparison. I am getting
correct numbers in LY YTD column, but when I click on the Year field, number
disappears and I get only hyphen.

Request anyone to help me out.

Thanks,

vidhya

12 Replies
vikasmahajan
Honored Contributor III

Re: Issue with YTD value

This means you need apply calender and check with financial year is available in your calender.

Vikas

MVP
MVP

Re: Issue with YTD value

Hi,

You need to exclude the selections on date fields then you will get the values

Example:

Sum({<Year=, Month=, Quarter=, Date={'Some Condition'}>})

Check this link for some sample expressions.

Set Analysis for certain Point in Time

thabang_petje
Contributor III

Re: Issue with YTD value

Hi,

You have to create a calendar whereby you store all the dates in one table and than link that to a key in the table you want to use. Could be a fact that has the corresponding dates

balrajahlawat
Esteemed Contributor

Re: Issue with YTD value

Jagan have shared the solution with his posted article which easily explained you set analysis for these YTD, WTD, MTD etc calculations which is always useful.

You can go through this as well:

YTQ, QTD, MTD and WTD

Not applicable

Re: Issue with YTD value

Hi,

I have used Master Calendar in my data model.

My Year is Financial Year 15-16, 14-15, 13-14 etc.

Now, I have to comparison for 15-16 vs 14-15.

When no year is selected, I get the numbers properly in Previous year YTD column,

but when I select  15-16 from the list box, PY column gives only hyphen (rather it should give numbers for 14-15).

When I select both 15-16 and 14-15, I get the right numbers.

Is there a way to tackle this problem?

Thanks in advance,

Vidhya

Re: Issue with YTD value

Hi,

what is your expression??

can you post your expression??

Regards

Regards,
Prashant Sangle
Not applicable

Re: Issue with YTD value

Hi,

sum({<Date={">=$(vLYMinDate)<=$(vLYMaxDate)"}>}Exit_Count)/

(((sum({<Date={"$(vLYMinDate)"}>}OpeningBalance)+ sum({<Date="$(vLYMaxDate)"}>}ClosingBalance))/2))

Basically I am calculating the Attrition YTD%.

$(vLYMinDate)= min({<FIN_YEAR_NO={"$(vPrevYear)"}>}Date)

$(vLYMaxDate)= max({<FIN_YEAR_NO={"$(vPrevYear)"}>}Date)

$(vPrevYear)=max(FIN_YEAR_NO)-1

FIN_YEAR_NO will have values like 2016, 2015, 2014, etc.

Thanks,

Vidhya

ankitaag
Contributor III

Re: Issue with YTD value

Hi Vidhya,

If you do not have any other filters except the Fiscal Year then you can wite the exp as below:

sum({1<Date={">=$(vLYMinDate)<=$(vLYMaxDate)"}>}Exit_Count)/

(((sum({1<Date={"$(vLYMinDate)"}>}OpeningBalance)+ sum({1<Date="$(vLYMaxDate)"}>}ClosingBalance))/2))


OR if you have othe filters also. the you can write it as :


um({1<Date={">=$(vLYMinDate)<=$(vLYMaxDate)"},[Financial Year]=>}Exit_Count)/

(((sum({1<Date={"$(vLYMinDate)"},[Financial Year]=>}OpeningBalance)+sum({1<Date="$(vLYMaxDate)"},[Financial Year]=>}ClosingBalance))/2))


Thanks and Regards,

Ankita

Not applicable

Re: Issue with YTD value

Hi Ankita,

I tried it.. It doesn't work!! Because I am applying filter on dates also in the set. so, even if I freeze, Year field, no change is seen.

Thanks,

Vidhya

Community Browser