topic Re: Text Object in QlikView

Text Object

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

I am trying to get the sum of sales for the latest month in a text box, but I am getting the sum of the year of year to date figure.

I have in the following in script:

Date(MonthStart(Date#([FY KPI], 'DD-MM-YYYY')), 'MM-YYYY') as FYKPIMonthYear

I have set up a variable:

vMaxBYM

=max(FYKPIMonthYear)

In the text object, I have the following which is resulting in year to date figures instead of the latest month:

=NUM(Sum ({<FYKPIFiscalYear = {$(=Max(FYKPIFiscalYear))},FYKPIMonthYear={"=$(vMaxBYM)"}>} Sales,'#,##0','.',',')

Any idea of the correct expression in the text object to return the latest month's figure?

Thanks

Re: Text Object

Anil_Babu_Samineni — Thu, 06 Apr 2017 03:56:17 GMT

May be this?

=Num(Sum ({<FYKPIFiscalYear = {"$(=Max(FYKPIFiscalYear))"},FYKPIMonthYear={"=$(vMaxBYM)"}>} Sales),'#,##0')

=Num(Sum ({<FYKPIFiscalYear = {"$(=Max(FYKPIFiscalYear))"},FYKPIMonthYear={"=$(=vMaxBYM)"}>} Sales),'#,##0')

Re: Text Object

shamitshah — Thu, 06 Apr 2017 04:33:24 GMT

Hi Anil,

Both are returning year to date figures.

I tried the following which seems to work:

=NUM(Sum ({<FYKPIFiscalYear = {$(=Max(FYKPIFiscalYear))},FYKPIMonthYear={">=$(vMaxBYM)"}>} Sales),'#,##0.00')

Now, I am trying to get the comparative for the prior year (same month) which is where I am having an issue. Any idea?

Thanks.

Re: Text Object

Anil_Babu_Samineni — Thu, 06 Apr 2017 06:25:10 GMT

That;s decimal format issue. That's not big deal

try for prior year like below. It will display last year of same month data and change variable to same as year with prefix of -1

=NUM(Sum ({<FYKPIFiscalYear = {"$(=AddYears(Max(FYKPIFiscalYear),-1))"},FYKPIMonthYear={">=$(vMaxBYM)"}>} Sales),'#,##0.00')

Re: Text Object

sunny_talwar — Thu, 06 Apr 2017 10:02:54 GMT

May be this

Sum({<FYKPIFiscalYear = {$(=Max(FYKPIFiscalYear))}, FYKPIMonthYear = {">=$(=Date(AddYears(vMaxBYM, -1), 'MM-YYYY'))"}>} Sales)

Re: Text Object

shamitshah — Fri, 07 Apr 2017 01:54:43 GMT

Hi Sunny & Anil

Please see attached sample.

I now have 2 text objects - one showing the year to date figure and the other showing the month to date figure for the current year.

I am having an issue with getting the month to date figure for the prior year in a text box (i.e for April 2016 which should be 300)

Anil, I did not quite follow your suggestion.

Any idea?

Thanks

Re: Text Object

shamitshah — Fri, 07 Apr 2017 02:00:13 GMT

The first text box should read Sales YTD and not Sales MTD.

Re: Text Object

Anil_Babu_Samineni — Fri, 07 Apr 2017 02:07:23 GMT

May be use for MTD
=Sum({<FYKPIMonthYear = {'$(=Min(FYKPIMonthYear,4))'}>}Sales)

Re: Text Object

shamitshah — Fri, 07 Apr 2017 02:19:29 GMT

I should clarify further - ideally it should update automatically with the figures for max month of the current year and the corresponding period for the prior year as and when the data is reloaded.

Thanks

Re: Text Object

sunny_talwar — Fri, 07 Apr 2017 08:01:53 GMT

May be this

=Num(Sum({<FYKPIMonthYear = {"$(=Date(AddYears(vMaxBYM, -1), 'MM-YYYY'))"}>} Sales),'#,##0.00')

Re: Text Object

shamitshah — Fri, 07 Apr 2017 08:41:00 GMT

Thanks Sunny!

I also tried the following and it seems to work:-

- Created another variable

vMinBYM =date(addmonths(vMaxBYM,-12),'MM-YYYY')

- Then use the following expression

=Num(SUM({<FYKPIFiscalYear = {$(=Max(FYKPIFiscalYear)-1)}, FYKPIFiscalMonth= ,FYKPIMonthYear={'$(vMinBYM)'}>}Sales),'#,##0.00')

Re: Text Object

sunny_talwar — Fri, 07 Apr 2017 09:31:07 GMT

Not sure why you need set analysis condition for FYKPIFiscalYear. Year is already handled by the FYKPIMonthYear as this include both month and year information. But having it in there doesn't really harm...