How to code Field name in Set expression Dynamically?

[#1]

Hi All,

I am trying to write a set expression in such a way that my field name sahould change dynamically based on Dimension

Example:

Expression : sum({<Flag_1={'1'}>}Sales)

I have created an Inline table which has no link with my dashboard and it has values like 'Flag' = 1,2,3,4,5,6,7,8,9,10.... so on till 100

Here is my data:

Purchase Year	Sales	Flag_1	Flag_2	Flag_3	Flag_4	..........	Flag_100
2008	20	1	0	0	1		1
2009	400	1	1	1	0	0	0
2010	4547	0	0	1	1	0	0
2011	2563	1	0	0	0	1	1

and I want values only for Flag_1.....100 = 1

Want to create a line chart with Flag as dimension and based on my dimension values my field 'Flag_' should change

for example:

                  sum({<'Flag_' & Flag={'1'}>}Sales)

doing this I can easily avoid nested if condition, but the above expression is not working at all.

Any help will be highly appreciated

Rob Wunderlich

Steve Dark Massimo Grossi Miguel A. Baeyens

[alexandros17]

Use a pivot table then drag your flag dimension to the top right ...

[jonathandienst]

Hi

I would crosstable load this data so that it is truely dynamic. Something like:

CROSSTABLE (FlagType, FlagValue, 2)

LOAD PurchaseYear,

     Sales,

     Flag_1,

     Flag_2,

     ....

     Flag_100

FROM .....

Now you  will have a FlagType field containing Flag_1, Flag_2.... and a FlagValue field containing the flag value in addition to the normally loaded fields (year and sales). So your expression is then:

     sum({<FlagValue={'1'}>} Sales)

And you can render the result in a crosstable easily with a pivot table (with FlagType as a dimension), should you so need.

HTH

Jonathan

[#4]

Hi Alessandro,

Thanks for the reply but 'Flag' has no link with my data and I don't think pivot table will help.

Is there any way to dynamically change the field name('Flag_') in set expression?

Regards,

Sagar Gupta

[#5]

Hi Jonathan,

Thanks for the reply but crosstable will not help in my case.

Is there any way to dynamically change the field name('Flag_') in set expression?

Regards,

Sagar Gupta

[jerrysvensson]

Not sure I fully understand your need.

Use variables like : Set vOne = 1

sum({<Flag_$(vOne)={'1'}>}Sales)

Would that help?

[#7]

Hi Jerry,

Thanks for your reply

You correctly understood my problem. I am trying to do what you have mentioned but it's not working and I have to make it work somehow or else i have to write 400+expressions 😞

Also, I can't pass a variable(vOne) as it will always return a single value, instead i want to pass my dimension field

Let me know if you have some answer

Regards,

Sagar Gupta

[#8]

Hi,

What Jerry Svensson suggested works perfectly.

The attached app will help, just select which flag you want from the list and the dynamic expression will get the results

[#9]

Hi,

Thanks for explaining it nicely

But in my case i don't want to make any selection. and i want to pass dimension field name instead of variable.

Thanks in advance

Regards,

Sagar Gupta