topic Re: Only or Total in QlikView

Only or Total

cbaqir — Wed, 23 Nov 2016 16:18:21 GMT

Select AU_NUM 848030.

In the Year End Forecast chart, I want the FY Budget to show 7325705 but it's summing the three months. What modifier should I use and how?

Frank_Hartmann — Wed, 23 Nov 2016 16:26:24 GMT

try this for FY Budget expression:

aggr(sum(FY_BUDGET_AMT),TRX_DATE,AU_NUM)

hope this helps

cbaqir — Wed, 23 Nov 2016 17:22:51 GMT

Thanks, Frank. Second question... now I want to only show the result for the max(TRX_DATE).

How can I use max(TRX_DATE) with the expression of =sum({$}FY_FCST_AMT)+sum({$}YTD_GL_AMT) ? !

Frank_Hartmann — Wed, 23 Nov 2016 17:31:13 GMT

perhaps this:

max(aggr(sum({$}FY_FCST_AMT)+sum({$}YTD_GL_AMT) ,TRX_DATE,AU_NUM))

hope this helps

cbaqir — Wed, 23 Nov 2016 17:32:01 GMT

Perfect, thanks!

sunny_talwar — Wed, 23 Nov 2016 17:42:34 GMT

Do you need the value for the max date or do you need the max value? If you need the value associated with max date, then try this

FirstSortedValue(aggr(sum({$}FY_FCST_AMT)+sum({$}YTD_GL_AMT) ,TRX_DATE,AU_NUM), -aggr(TRX_DATE, TRX_DATE,AU_NUM))

When 100000 is selected, max is max from the three dates and FirstSortedValue is value associated with max date