topic Re: Issue with YTD value in QlikView

Issue with YTD value

Wed, 23 Dec 2015 06:10:16 GMT

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

Re: Issue with YTD value

vikasmahajan — Wed, 23 Dec 2015 06:15:42 GMT

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

Vikas

Re: Issue with YTD value

jagan — Wed, 23 Dec 2015 06:20:14 GMT

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

Re: Issue with YTD value

Anonymous — Wed, 23 Dec 2015 06:20:21 GMT

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

Re: Issue with YTD value

Anonymous — Wed, 23 Dec 2015 06:26:57 GMT

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

Re: Issue with YTD value

Tue, 29 Dec 2015 09:51:04 GMT

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

PrashantSangle — Tue, 29 Dec 2015 09:55:50 GMT

Hi,

what is your expression??

can you post your expression??

Regards

Re: Issue with YTD value

Tue, 29 Dec 2015 10:07:01 GMT

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

Re: Issue with YTD value

ankitaag — Tue, 29 Dec 2015 10:26:50 GMT

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

Re: Issue with YTD value

Tue, 29 Dec 2015 10:46:17 GMT

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

Re: Issue with YTD value

ankitaag — Tue, 29 Dec 2015 10:54:28 GMT

Hi Vidhya,

Can you post a sample application. That would be much helpful.

Thanks and Regards,

Ankita

Re: Issue with YTD value

Anonymous — Tue, 29 Dec 2015 11:03:57 GMT

try this:

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

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

Re: Issue with YTD value

Tue, 29 Dec 2015 11:11:28 GMT

Nope. it doesn't work