Formula difficulties - compare two values

[#1]

Hi all,

I have two parameters in my dashboard, that I would like to use as a filter.

So, when the salesdepartment (CI_AFD) that is selected = 'team1', I want to only see products with the value 'team1' (afd_ci)

I thought to fix it by using this as formula:

= sum({$<CI_AFD = {$(afd_ci)} >}OMZET) 

but that does not work.

Any ideas?

greetings,

Wouter

[Miguel_Angel_Baeyens]

Hi,

Try using single quotes, because the value in variable is a text string:

=Sum({< CI_AFD = {'$(afd_ci)'} >} OMZET) 

Hope that helps.

Miguel

[#3]

Hi Miguel,

Unfortunately it didn't work. ( I don't see data).

What works is if I use this formula:

=sum({$<CI_AFD  = {'CI - CendrisZakelijk'}>}OMZET) 

However, instead of hard coding the variabel, I want to use the selection from the field ci_afd

Any other ideas?

[Miguel_Angel_Baeyens]

Hi Wouter,

Then you should define your variable in the Settings menu, Variable Overview as

=Chr(39) & Concat(CI_AFD, Chr(39) & Chr(44) & Chr(39)) & Chr(39)

So if you put the variable in a text object you see values single quoted and comma separated, which is a format ready to use in set analysis: 'Value','Value2','Value3'

Using the expression suggested should now work

Hope that helps.

Miguel

[#5]

Hi Miguel,

It does change the outcome. However it gives an empty table.

Should I make a new LET or SET variabel?

thanks for the efforts!!

Wouter

[Miguel_Angel_Baeyens]

Hi Wouter,

You don't need to use LET or SET because you are creating the variable in run time. Check the application for a dummy example on how this works.

Hope that helps.

Miguel

[#7 · Accepted solution]

The following now works:

=Sum({$< CI_AFD = afd_ci >} OMZET) 

Thanks you Miguel, l wouldn't have solved it without your help!!

Wouter