topic Re: Set Modifier Question in QlikView

Set Modifier Question

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

My current expression for a Gauge Chart is

=num(sum({<[Production Area]={'Upstream'}>}RFT)/Count({<[Production Area]={'Upstream'}>}RFT),'#.##%')

I want to also filter to the current month, but keep getting errors

I want it to end up something like this

=num(sum({<[Production Area]={'Upstream'}, [Date Document Final approved] >= MonthStart(now()), [Date Document Final approved] <= MonthEnd(now())>}RFT)/Count({<[Production Area]={'Upstream'}, [Date Document Final approved] >= MonthStart(now()), [Date Document Final approved] <= MonthEnd(now())>}RFT),'#.##%')

I understand the statements need wrapping up with {} but cant get it to work, I will keep trying, but could do with some pointers or help.

Could anyone please help me out.

Much appreciated.

Spikenaylor

Re: Set Modifier Question

Anonymous — Tue, 16 Aug 2016 14:59:14 GMT

try this !! But I doubt on the Date formats .........

=num(sum({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Monthstart(now())<=$(=MonthEnd(now())”}>}RFT)

count({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Monthstart(now())<=$(=MonthEnd(now())”}>}RFT) ,’#.##%’)

Re: Set Modifier Question

Anonymous — Tue, 16 Aug 2016 15:00:43 GMT

Hi,

Try it.

=num(sum({<[Production Area]={'Upstream'},[Date Document Final approved] ={ ">=MonthStart(=now())<= MonthEnd(=now())">}RFT)

/Count({<[Production Area]={'Upstream'}, [Date Document Final approved] ={ ">=MonthStart(=now())<=MonthEnd(=now())">}>}RFT),'#.##%')

Regards

Re: Set Modifier Question

Anonymous — Tue, 16 Aug 2016 15:03:17 GMT

Try this as well ...with date formats

=num(sum({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Date(Monthstart(now()),’MM/DD/YYYY’)<=$(=Date(MonthEnd(now()),’MM/DD/YYYY’)”}>}RFT)

count({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Date(Monthstart(now()),’MM/DD/YYYY’)<=$(=Date(MonthEnd(now()),’MM/DD/YYYY’)”}>}RFT) ,’#.##%’)

Re: Set Modifier Question

vishsaggi — Tue, 16 Aug 2016 15:19:51 GMT

Try this:

=num(sum({<[Production Area]={'Upstream'}, [Date Document Final approved] = { ">= $(= Date(MonthStart(now()), 'MM/DD/YYYY')) <= $(= Date(MonthEnd(now()), 'MM/DD/YYYY')) "} >}RFT)

Count({<[Production Area]={'Upstream'}, [Date Document Final approved] = { ">= $(= Date(MonthStart(now()), 'MM/DD/YYYY')) <= $(= Date(MonthEnd(now()), 'MM/DD/YYYY')) "}>}RFT),'#.##%')

Re: Set Modifier Question

spikenaylor1 — Wed, 17 Aug 2016 06:10:42 GMT

Thanks for the help but the formulas are counting all the data not filtering down to the current month.

I have broken it down into two text boxes to see the formula results.

both give results for all data within Upstream.

=Count({<[Production Area]={'Upstream'}, [Date Document Final approved] = { ">= $(= Date(MonthStart(now()), 'MM/DD/YYYY')) <= $(= Date(MonthEnd(now()), 'MM/DD/YYYY')) "}>}RFT)

produces a count of 338 which is the total count for all the data within Upstream.

on the source data I filter down to the current month and my result should be 5

the formulas date filtering doesn't seem to be working for me.

Re: Set Modifier Question

Anonymous — Wed, 17 Aug 2016 08:37:38 GMT

count({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Date(Monthstart(now()),’MM/DD/YYYY’)<=$(=Date(MonthEnd(now()),’MM/DD/YYYY’)”}>}RFT),’#.##%’)

Here the filter only applies to the field :[Date Document Final approved] and [Production Area]

Not on other fields ,.....

How do you select this Month (Is this Another field) ?

on the source data I filter down to the current month and my result should be 5

the formulas date filtering doesn't seem to be working for me.

Re: Set Modifier Question

spikenaylor1 — Wed, 17 Aug 2016 12:57:37 GMT

I was referring to checking the source data within the database to confirm results.

So the formula returns the number 343 which is the count of all records for all dates[Date Document Final Approved] in Upstream [Production Area].

it should be returning the number 5 which is the count of all records for the dates [Date Document Final Approved] that are >= MonthStart(Now()) and <= MonthEnd(Now()) in Upstream

So the formula seems to be filtering for the [Production Area] but not for the [Date Document Final Approved]

Sorry for any confusion.

Re: Set Modifier Question

Anonymous — Wed, 17 Aug 2016 15:33:13 GMT

So far i know the above expression will give only for the month AUGUST and [Production Area] ='upstream'

=AGGR(Count({<[Production Area]={'Upstream'}, [Date Document Final approved] = { ">= $(= Date(MonthStart(now()), 'MM/DD/YYYY')) <= $(= Date(MonthEnd(now()), 'MM/DD/YYYY')) "}>}RFT),[Production Area])

Or you have to mention the other fields for Ignoring them .........

Re: Set Modifier Question

spikenaylor1 — Wed, 17 Aug 2016 17:10:56 GMT

Yes I agree

The formula should show results for within the current month of August and within upstream but it shows the result for all records within upstream ignoring the month filtering

Could it be something about the [date document final approved] format

Does that field need to be formatted correctly in the load script

Currently it shows in a table as did/mm/yyyy 00:00:00

I have tried changing it in the load using date function to show in a table as did/mm/yyy

But the formula still ignores the month filter

Regards

Re: Set Modifier Question

Anonymous — Thu, 18 Aug 2016 07:21:54 GMT

we will give another try.....

=Count( $ {<[Production Area]={'Upstream'}, [Date Document Final approved] = { ">= $(= Date(MIn([Date Document Final approved]), 'MM/DD/YYYY')) <= $(= Date(Max([Date Document Final approved]), 'MM/DD/YYYY')) "}>}RFT)

Re: Set Modifier Question

spikenaylor1 — Thu, 18 Aug 2016 10:09:54 GMT

Nope, that just throws up an error.

I have tried another route I found on the internet just to try and prove concept.

in my load script I have

set vCurrentMonthStart = Date(MonthStart(now()));

in a textbox with this formula

=$(vCurrentMonthStart)

01/08/2016 is shown - this is the monthstart

in another text box, i have the following formula

=Count({< [Date Document Final approved] = {"$(=vCurrentMonthStart)"}>}RFT)

The resulting value is a count of all records in the database, in this case 1100 when it should be 5, it still does not only count records from 01/08/2016

the [Date Document Final approved] field when shown in a table object is listed in the following format

dd/mm/yyyy hh:mm:ss

I have tried using a

Date([Date Document Final Approved]) as DateDocumentFinalApproved;

in the load script and this shows in a format of

dd/mm/yyyy in the same table object

I have tried both ways but still cannot get the set modifier expression to only count records from a certain date.

if necessary I can post an example record set if that can be any help.

many thanks for all your ongoing help and advice