Straight table values filtering

[#1]

Hi.

I've got an issue with one table where I want to filter values that are not corresponding to my query. Or more exactly, the current query I make let appear all the values.

Here is the screenshot of the table.

In this table, the first 2 columns are dimensions (customer code and date of delivery)

the 3rd one is based on this expression :

count(DISTINCT {$<[Entete Souche]={'Facturation'}, [Entete Type]={6},[Entete Provenance]={'Avoir'},[Entete Type Avoir]={"GLOBAL"}>} CodeDocument)

and works perfectly

and the 4th is currently :

=count({1<[Entete Souche]={'Facturation'}, [Entete Type]={6},[Entete Provenance]={'Facture'}>} DISTINCT CodeDocument)

This expression is giving me all the deliveries within the database but I want only to get those correspondig to the customers that have an "Avoirs" (column 3 > 0). I've tried different solutions but none work for now.

Any idea ?

[Gysbert_Wassenaar]

Maybe like this:

=count({1<[Entete Souche]={'Facturation'},

          [Entete Type]={6},

          [Entete Provenance]={'Facture'},

          CodeDocument = {"=$(count(DISTINCT {$<[Entete Souche]={'Facturation'},

                  [Entete Type]={6},

                  [Entete Provenance]={'Avoir'},

                  [Entete Type Avoir]={'GLOBAL'}>} CodeDocument)>0)"}

          >} DISTINCT CodeDocument)

[#3]

The code you add return 0 as code document but I think this is the idea to return the documents codes based on current selection.

[#4]

Hi,

I think this might help you..

In the query, give an alias to the field you are fetching,

ex)

select name as NAME_TEMP.

Then, in your chart, populate 'NAME_TEMP' instead of name. Note that no other query should have this alias for the same field.

Then, go to Settings->Document Properties->Tables-> check Loosely Coupled for all tables.

Hope this solves your problem.

Regards,

Anju

[#5]

If I do that I loose all the links among the different tables in the data structure and so the queries in other chart no more work :s

[#6]

Can you share your app please?

[#7]

You can check this doc.

[#8]

note that in my doc,

i have modified the expressions field...in one chart, I populate payingbase_subscriber, in the other I populate payingbase_vod etc...all fetched from independent queries, but my dimension fields have same alias, i.e partner and batchmonth,,and the filters are these two fields...so, on making selections to these fields, since the two filters are common to all queries, the link between tables won't be lost.

Regards,

Anju

[#9]

Here is the file. I take a look at your example.

The table I'm talking about is on the second tab, down right, called Avoir.

So I make the selection of the weeks on wich I do the control and I verify with this specific table whether I had multiple orders the day of delivery of the original invoice on which there is a credit note placed.

Thank you for your help.

Sebastien

[Gysbert_Wassenaar · Accepted solution]

Maybe we're making this harder than it needs to be. Does this give the result you want:

=if(Avoirs>0, count({1<[Entete Souche]={'Facturation'}, [Entete Type]={6},[Entete Provenance]={'Facture'}>} DISTINCT CodeDocument))