YTD mismatch value

[Karim_Khan]

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,

KK

celambarasan‌

qlikrajan‌jimhalpert‌New to QlikView‌

[Anil_Babu_Samineni · Accepted solution]

May be remove DISTINCT and use

Or

Try some thing Like below

Sum(TOTAL MTD)

MTD is the label for expression

[#3]

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

[sasikanth]

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?

OR

Try this

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

[adamdavi3s]

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)

[Karim_Khan]

TNX