topic Re: YTD mismatch value in QlikView

YTD mismatch value

Karim_Khan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

It would be silly but am unable to get proper YTD calculation.As per YTD formula I am getting different value.

YTD:-

=COUNT(DISTINCT{<FIN_YEAR_NO={$(=$(vMaxYear))},QUARTER_NO={'<=$(vMaxQtr)'},FIN_MONTH_NO={'<=$(vMaxMon)'},FIN_MONTH=,MONTH=,MONTH_NO=,FIN_QUARTER=>} ORDER_NUM)

MTD:-

=COUNT(DISTINCT{<FIN_YEAR_NO={$(=$(vMaxYear))},FIN_MONTH_NO={$(=$(vMaxMon))},FIN_MONTH=,MONTH=,MONTH_NO=,FIN_QUARTER=>} ORDER_NUM)

MTD is working fine and giving proper value but while cross checking YTD value am getting the big difference between total MTD for Financial year 16-17.

E.g:

YTD - 2793

MTD

APR - 0

MAY - 0

JUN - 0

JUL -71

AUG - 574

SEP - 564

OCT - 583

NOV - 740

DEC -1060

JAN -758

FEB -558

Total YTD will be addition of all months(MTD) which should come as 4908 but it is taking the count as 2793.

I have checked each and every row but don't know where is a loophole either in calculation or understading?

Regards,

celambarasan‌

qlikrajan‌jimhalpert‌New to QlikView‌

Re: YTD mismatch value

Anil_Babu_Samineni — Wed, 01 Mar 2017 06:39:40 GMT

May be remove DISTINCT and use

Try some thing Like below

Sum(TOTAL MTD)

MTD is the label for expression

Re: YTD mismatch value

Wed, 01 Mar 2017 07:06:20 GMT

can you attach your sample document, so that it will be helpful to check the logic and try some alternative codes

Re: YTD mismatch value

sasikanth — Wed, 01 Mar 2017 07:37:40 GMT

HI,

try below

YTD:-

=COUNT(DISTINCT{<FIN_YEAR_NO={$(=$(=vMaxYear))},QUARTER_NO={'<=$(=vMaxQtr)'},FIN_MONTH_NO={'<=$(=vMaxMon)'},FIN_MONTH=,MONTH=,MONTH_NO=,FIN_QUARTER=>} ORDER_NUM)

why didn't you go for a Date instead of year, quarter,Month with variables?

Try this

Use Count(DISTINCT {< date={">= $(= MonthStart(max(date))) <= $(=max(Date)) "},FIN_MONTH=,MONTH=,MONTH_NO=,FIN_QUARTER >}ORDER_NUM)

Re: YTD mismatch value

adamdavi3s — Wed, 01 Mar 2017 10:10:52 GMT

As Anil says I think you need to remove your distinct, I am guessing you have the same order numbers used in different months?

I guess you could prove this by using something like this to sum the aggregation of the forumla for each month:

SUM(AGGR(COUNT(DISTINCT{<FIN_YEAR_NO={$(=$(vMaxYear))},QUARTER_NO={'<=$(vMaxQtr)'},FIN_MONTH_NO={'<=$(vMaxMon)'},FIN_MONTH=,MONTH=,MONTH_NO=,FIN_QUARTER=>} ORDER_NUM),FIN_MONTH_NO)

Re: YTD mismatch value

Karim_Khan — Wed, 01 Mar 2017 12:37:24 GMT

TNX