topic Re: Re: LYTD in QlikView

LYTD

krishna20 — Mon, 04 Aug 2014 12:19:27 GMT

Hi Friends,

i'm facing problem in LYTD.in my expression last year part is working but the month part is not working.

CLM_DATE format DD/MM/YYYY

CLM_SETL_DTYear format is 2014,2013,2012.....

CLM_SETL_DTMonth format is JAN,FEB,MAR,,.....

can anyone pls help me out.

if(

vOptionCurrency = 1, (Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE ={"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {1}>}CS_AMT_LC_1) //Payment

-(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {3}>}CS_AMT_LC_1))) //Recovery

-(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {2}>}CS_AMT_LC_1) //Reversal Of Payment

-(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {4}>}CS_AMT_LC_1))), //Reversal of Recovery

if(

vOptionCurrency = 3, (Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {1}>}CS_AMT_LC_3) //Payment

-(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {3}>}CS_AMT_LC_3))) //Recovery

-(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {2}>}CS_AMT_LC_3) //Reversal Of Payment

-(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {4}>}CS_AMT_LC_3)))) ) //Reversal of Recovery

Regards

Krishna

Re: LYTD

Tue, 05 Aug 2014 06:18:44 GMT

Hi,

As i saw your expression, want to know CLM_DATE is it a DATE or MONTH Field. If its date , then as per your expression you are passing month which might be the reason its not working.

If its a month try >= instead of = in set analysis.

Re: LYTD

Anonymous — Tue, 05 Aug 2014 06:44:10 GMT

Hi,

Your month comparison looks weird to me. CLM_DATE is in DD/MM/YYYY format and you are comparing with Num(Month(Addmonths(Max(CLM_DATE),-12))) which is in MM format.

You may either replace CLM_DATE with CLM_SETL_DTMonth in set analysis comparison (see below)

Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)}, CLM_SETL_DTMonth={"<=$(=Month(AddMonths(Max(CLM_DATE),-12)))"}, CS_EST_TYPE = {1}>}CS_AMT_LC_1)

Or use Today function to compare month (see below)

Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)}, CLM_DATE ={"<=$(=Date(AddMonths(Today(),-12)))"}, CS_EST_TYPE = {1}>} CS_AMT_LC_1)

Re: LYTD

krishna20 — Wed, 06 Aug 2014 06:01:22 GMT

Hi Yashwin,

CLM_DATE is date filed in the format DD/MM/YYYY.

Regards

Krishna

Re: LYTD

krishna20 — Wed, 06 Aug 2014 06:07:52 GMT

Hi satyadev,

By using this expression i'm reaching the database value.But not exact value. I tried which you suggested me,but value differs much.

(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE ={"(<=$(=Date(AddMonths(max(CLM_DATE),-12),'DD/MM/YYYY')))"},CS_EST_TYPE = {1}>}CS_AMT_LC_1)

My value 29,454,249 .08

DB Value 29,451,049.08

Shall i expect any other solution for this.

Regards

Krishna

Re: LYTD

Anonymous — Wed, 06 Aug 2014 06:28:49 GMT

Hi Kalyan Krishna,

Try removing "<=" and take CLM_SETL_DTMonth as suggested by satyadev...

regardz,

Vaibz..

Re: LYTD

krishna20 — Wed, 06 Aug 2014 06:42:51 GMT

Hi,

I did as u suggested.I'm getting zero.

Regards

Krishna

Re: LYTD

Anonymous — Wed, 06 Aug 2014 07:06:26 GMT

So basically there is data difference of 3200 between your value and DB value. Can you do simple testing and see where is the problem. Create straight table with CLM_DATE in dimension and above script in expression. Also add another script Sum(CS_AMT_LC_1) and see where is the gap.

Re: LYTD

krishna20 — Wed, 06 Aug 2014 07:45:26 GMT

Hi Satyadev,

My expression is calculation of Payment,Reversal of Payment,Recovery, and Reversal of Recovery.How can i compare total with this expression amount.

Regards

Krishna

Re: LYTD

Anonymous — Wed, 06 Aug 2014 08:13:40 GMT

Krishna,

Just test Payment data first with my earlier suggestion. If you can figure out this then others can be resolved easily. If possible share your apps with limited and scrambled data to look into exact issue.

Re: Re: LYTD

krishna20 — Wed, 06 Aug 2014 08:28:54 GMT

Hi Satyadev,

Pls find the below attachment and guide me to solve this issue.

Pls clear the application once you opened it and select QIC,Abhudhabi.

Regards

Krishna

Re: LYTD

Anonymous — Wed, 06 Aug 2014 09:52:32 GMT

I did checked with simple sum and it is matching with second last set analysis script (see below the set analysis script and matching table).

Re: Re: LYTD

krishna20 — Wed, 06 Aug 2014 11:22:30 GMT

Hi, Satyadev,

Thanks for your reply,

I did exactly what you guided me.But,the values are still differ.I'm unable to get exact output.

Regards

Krishna

Re: Re: Re: LYTD

Anonymous — Thu, 07 Aug 2014 06:50:15 GMT

Kalyan,

See attachment. In Sheet38, I did some testing and looks like the LYTD logic is fine. I have taken simple sum of values and LYTD logic and both columns are matching for 2013.

Let me know if I am missing something.

Re: LYTD

krishna20 — Mon, 11 Aug 2014 07:04:44 GMT

Thank You satyaDev,

Sorry for late reply..

Thanks and Regards

krishna