Multiple possible values function usage in set analysis

[San_M]

Hi Team,

I am not sure if this question has already been asked. If yes, apologies.

I am trying to use the Possible values function in my set expression for multiple fields.

It works fine, when the function is used only once with one field. But does not work when I use two or more conditions.

An Example to illustrate : I have 3 fields A1, B1, C1  which belong to a larger set A, B, C

I want to calculate the Count(Serial Numbers) when the selected value of A1 is in the set A, B1 is in set B or C1 is in Set C

I used the following condition :

Count ( {< A1 = P(A) > + <B1 = P(B)> + <C1=P(C)> }Distinct SerialNumbers)

The above Expression does not work

whereas 

Count ( {< A1 = P(A) >} Distinct SerialNumbers)  works fine.

Any suggestions?

Thanks,

Saranya

[marcus_sommer]

It's not really clear for me how the multiple conditions should work - AND or OR or XOR - and how the data and their subsets are related to eachother. But maybe you could try it in this way:

Count ( {< A1 = P(A) > * <B1 = P(B)> * <C1=P(C)> }Distinct SerialNumbers)

or maybe by nesting them like:

Count ( {< A1 = P({< B1 = P(B)>} A) >} Distinct SerialNumbers)

- Marcus

[anushree1]

Could you share a sample application to better understand the situation here

[sunny_talwar]

Is this really an or condition? Maybe you just need this

Count({<A1 = P(A), B1 = P(B), C1 = P(C)>} DISTINCT SerialNumbers)

But like others have mentioned, it is difficult to know what you are trying to do without more details. 

[San_M]

Hi Marcus /Sunny/Anushree,

I am unfortunately not authorised to export the application so I think i will just explain my application in a little detail.

I have a sheet with multiple charts that displays data for the last 20 years. 

In the same sheet,  there is one particular line chart that is a rolling window. i.e it calculates tenement records for the last 90 days from all the dates in the 20 years span and displays the result.

So in my load script what i have done is to load data twice. Once for the last 20 years calculation (The main set A) and another one for the consecutive 90 days calculation.( Kind of like the subset A1)

I have filter panes that are fields from the last 20 years load like, Tenement Type, Tenement Holder Company etc.(I think i have about 7 fields like this).

I want my consecutive 90 day chart to reflect when i make selections from the above fields. 

In other words, I want to be able to relate both the the 20 yrs load data (A,B,C... )and consecutive 90 days load data (A1,B1,C1...).

I tried doing it in the load script, but i always received the synthetic key/circular loop error.

And hence attempting to do the same by using the possible values function.

Regards,

Saranya

[San_M]

So, the condition should be like, even if there are no selections done in the 20 years fields,  the chart should still be calculated and displayed.

If it still seems too hard to comprehend, i will try replicating by creating an app and then upload it.

[marcus_sommer]

I don't think I would try it in this way - I mean creating multiple (sub) datasets within an application. At the moment I could not think on one usecase in which it might be useful and/or necessary to create ones and to link them will be in each case a challenge.

Quite useful is it sometimes to create some island-tables to be able to create independent selections and/or to provide a certain usability - and there it's quite common to use p() and e() to associate them with the real datamodel.

In your case I think it should be possible with some set analysis which queries just a flag from the datamodel (if your 90 days back-view is always related to today or the max. date from the facts) or it queries the max. date from the given selection and goes then 90 days back - with the {1} as set modifier it will ignore all further selections.

- Marcus

[San_M]

Hi Marcus,

Thank you. I know it isn't the best coding practice. But to work out all calculations, this seemed the best bet. I am still working on optimization and the creation of an appropriate island table. Hopefully will be able to resolve that soon. 🙂

A little more on the coding , So the code currently generates all dates from today to the start of 20 years back (01/01/1999 - 14/05/2019) putting them in a field named BridgeDate and there is a function to calculate 90 days back for each of these days and is stored in a field named ApplicationDates
I have associated this field with BridgeDate.
And i thought that would suffice all my selections and calculations. But it doesnt. 😞
Regards,
Saranya