topic Re: Set formula with GetFieldSelections() in QlikView

Set formula with GetFieldSelections()

Anonymous — Thu, 19 May 2016 16:33:34 GMT

Hello,

I would like to do a difference when a criteria is equal to an other field.

I have two field of Date:

And when the field "Date" is equal to one of the data in the field "Value Date", this operation have to be done:

=Sum({<AsOf = , [AIR Import Data] = {"=GetFieldSelections(AsOF)"}>} Value_AIR)/1000000

- SUM({$<source = {'Costs_PC'}>}myVal) - SUM( { $< source = {'FTE_Activity'}>}myVal)/

SUM( TOTAL { 1< source = {'FTE_Activity'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)*

SUM( TOTAL { 1< source = {'Allocated_Costs'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)

But this part doen't work:

Sum({<AsOf = , [AIR Import Data] = {"=GetFieldSelections(AsOF)"}>} Value_AIR)/1000000

And i need to precise this parameter because it will sum all the data of Value_AIR for all AsOf.

How can i do that please ?

Thanks you in advance for your help,

Sébastien.

PS: AsOF is the name of the field 'Date' and AIR Import Data is the name of 'Value Date'

Re: Set formula with GetFieldSelections()

sunny_talwar — Thu, 19 May 2016 16:35:45 GMT

Can you try this:

Sum({<AsOf = , [AIR Import Data] = p(AsOF)>} Value_AIR)/1000000

Re: Set formula with GetFieldSelections()

trdandamudi — Thu, 19 May 2016 16:37:06 GMT

May be this:

Sum({<AsOf = , [AIR Import Data] = p (AsOF) }>} Value_AIR)/1000000

Re: Set formula with GetFieldSelections()

Anonymous — Thu, 19 May 2016 16:40:53 GMT

No it doen't work :/, it returns no values.

Do you have an other idea ? Then we could create a table which can be the intersection of the equal date between theses Field ?

Sébastien.

Re: Set formula with GetFieldSelections()

sunny_talwar — Thu, 19 May 2016 16:43:46 GMT

It doesn't because the AsOf you have selected doesn't match either of the two dates available. Is this based on year or month or some kind of range??

Re: Set formula with GetFieldSelections()

Anonymous — Thu, 19 May 2016 21:12:20 GMT

It is based on the exact value of the Field 'Value Date'.

I don't see how solve this 😕

Re: Set formula with GetFieldSelections()

Anonymous — Fri, 20 May 2016 06:36:00 GMT

No idea please ?

Thank you in advance for your help ,

Sébastien.

Re: Set formula with GetFieldSelections()

sunny_talwar — Fri, 20 May 2016 06:38:06 GMT

In the image you show that you selected 31/03/2016, but there is no 31/06/2016 to show in Value Date?

Re: Set formula with GetFieldSelections()

Anonymous — Fri, 20 May 2016 06:42:45 GMT

Sorry, now the Date 31/03/2016 is present into the fields:

And in the expression bellow:

=Sum({<AsOf = >} Value_AIR)/1000000

- SUM({$<source = {'Costs_PC'}>}myVal) - SUM( { $< source = {'FTE_Activity'}>}myVal)/

SUM( TOTAL { 1< source = {'FTE_Activity'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)*

SUM( TOTAL { 1< source = {'Allocated_Costs'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)

==> The field Value_AIR is dependent of the field Value_Date which is named 'AIR Import Data' in the Script

==> The field myVal is dependent of the field Date which is named 'AsOf' in the Script

Sébastien.

Re: Set formula with GetFieldSelections()

Kushal_Chawda — Fri, 20 May 2016 06:44:14 GMT

Is there any relation between these dates? If so you can try

Sum({<AsOf = , [AIR Import Data] = {"= [AIR Import Data]=AsOf "}>} Value_AIR)/1000000

Re: Set formula with GetFieldSelections()

sunny_talwar — Fri, 20 May 2016 06:47:38 GMT

Not sure I understand the reason for using this?

AsOf = {"=$(=GetFieldSelections(AsOf))"}

Can you try to replace this with :

AsOf = p(AsOf)

Re: Set formula with GetFieldSelections()

CELAMBARASAN — Fri, 20 May 2016 06:48:15 GMT

Try with Intersect operator (*), check the below expression

Sum({<AsOf = , [AIR Import Data] *= {"=GetFieldSelections(AsOF)"}>} Value_AIR)

Sum({<AsOf = , [AIR Import Data] *= P(AsOF)>} Value_AIR)

Re: Set formula with GetFieldSelections()

Anonymous — Fri, 20 May 2016 06:49:53 GMT

Thanks it works !

But now I want to simplify the field of Date

Can i create a field which will be the intersection of theses two fields ?

Sébastien

Re: Set formula with GetFieldSelections()

Kushal_Chawda — Fri, 20 May 2016 06:53:59 GMT

I did not get you. Can you elaborate?

Re: Set formula with GetFieldSelections()

Anonymous — Fri, 20 May 2016 06:56:39 GMT

Because Now i have two fields for Date here (Date and Value Data):

How can i simplify this into one field which will be the intersection of the Date present in theses two fields.

Thanks in advance,

Sébastien.

Re: Set formula with GetFieldSelections()

Kushal_Chawda — Fri, 20 May 2016 07:03:52 GMT

try this

Sum({<AsOf = , [AIR Import Data] *= {"= [AIR Import Data]=AsOf "}>} Value_AIR)/1000000

Re: Set formula with GetFieldSelections()

Anonymous — Fri, 20 May 2016 07:07:43 GMT

Yes, i'm ok with this .

But it is possible to create an other Table Like 'Date' which will be the intersection between the Date from 'Date' (named 'AsOF' into the Script) and 'Value Date' (named 'AIR Data Import' into the script).

Sébastien.

Re: Set formula with GetFieldSelections()

Kushal_Chawda — Fri, 20 May 2016 07:11:30 GMT

Yeah for that you can see this

Canonical Date