topic Text Object in QlikView

Text Object

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

Hi,

I have the following in a text object, but not getting any value

=num(AGGR(SUM(total{$<RecordMonthYr={"=$(vMaxRMY)"},SKH_Source={'SO'}>}SKH_Quantity),[Item Number]),'#,##0','.',',')

The variable vMaxRMY has been set as =date(max(RecordMonthYr),'MM/YYYY')

Any ideas?

Thanks

Shamit

Re: Text Object

vishsaggi — Sat, 16 Jul 2016 23:03:08 GMT

Hi Shah,

Is your recordMonthYr same format as your variabe vMaxRMY ?

SET vMaxRMY = "=date(max(RecordMonthYr),'MM/YYYY')" ;

And try this:

=num(AGGR(SUM(total{$< RecordMonthYr= {"$(vMaxRMY)"}, SKH_Source={'SO'}>} SKH_Quantity),[Item Number]),'#,##0','.',',')

These are references taken from HIC post: Dates in Set Analysis

Please look into this and try.

Re: Text Object

sunny_talwar — Sat, 16 Jul 2016 23:06:32 GMT

You don't have an outer Aggregation function, what exactly are you trying to get? Can you try one of these?

=Num(Sum({$<RecordMonthYr={"$(=vMaxRMY)"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

=Num(Sum({$<RecordMonthYr={"$(=$(vMaxRMY))"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

Re: Text Object

shamitshah — Sat, 16 Jul 2016 23:26:24 GMT

I am trying to get the number of Sales Orders for the latest month.

In the script, I have the following:

MonthName(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonthYr

I then set up the variable to get the max RecordMonthYr as =date(max(RecordMonthYr),'MM/YYYY')

In the text object, I wanted to get the number of Sales Orders for the latest month.

Thanks

Shamit

Re: Text Object

sunny_talwar — Sun, 17 Jul 2016 11:02:32 GMT

I think since you created your field RecordMonthYr using MonthName function, that is what you would need to use in your variable:

RecordMonthYr as = MonthName(Max(RecordMonthYr))

Alternatively, you can change how you create your field RecordMonthYr

Date([SKH_RecordDate] + MakeDate(2000,1,1), 'MM/YYYY') as RecordMonthYr

and then use your current variable definition:

RecordMonthYr as =Date(Max(RecordMonthYr), 'MM/YYYY')

The goal is to have same format on the Left and Right hand sides of the equation. Once you have the above two fixed, try using one of these expressions to see if it finally works or not

=Num(Sum({$<RecordMonthYr={"$(=vMaxRMY)"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

=Num(Sum({$<RecordMonthYr={"$(=$(vMaxRMY))"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

Re: Text Object

shamitshah — Wed, 20 Jul 2016 03:54:25 GMT

Thanks Sunny,

Your solutions works perfectly.

I changed the variable to = MonthName(Max(RecordMonthYr))

For the text object ==Num(Sum({$<RecordMonthYr={"$(=vMaxRMY)"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

Shamit