Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
spikenaylor1
Creator
Creator

Set Modifier Question

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

11 Replies
Anonymous
Not applicable

Hi

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)



spikenaylor1
Creator
Creator
Author

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