How to put formulas into set modifiers in Set Analysis?

[Anonymous]

Hello,

I want to customize labels & expressions dynamically based on selection. The label will displays all the 12 months of the selected year.

I set up a set analysis to calculate amount for each month accordingly based on the label, but cannot put the formula into the modifiers in set expression (eg: in the snapshot, to replace "2015_Jan"). Does anyone know to do it?

Lots of thanks.

[Or]

{<Year_month={$(=YourFormulaHere() }>} should work.

[andrey_krylov]

Hi, Angie, Or Shoham is right. I made some corrections, see attached

[Anonymous]

Hi Shoham,

I tried but it doesn't work.

[jonathandienst]

This all becomes so much easier if you convert the date fields into proper QV date (numeric) type during your load script...

[Anonymous]

Hi Andrey, what expression did you make the adjustment? I couldn't locate it.

[gawalimegha]

Hello Angie,

1) First create variable as

demoChk= =GetFieldSelections(YearMonth)

2)Then add below Expression in your chart

=sum({<YearMonth={'$(demoChk)'}>}[Fact.Total Profit])

3) Sample Output will be like this

[andrey_krylov]

Take another look, please. It seems I saved file with another name.

[Anonymous]

Hi Jonathan,

I convert it into Date format then apply the above instruction but could not get the output.

Convert in script:

Date(Date#([By Year] &'_'&[By Month],'YYYY_MMM'),'YYYY_MMM') as Year_month,

Adjust Expression

sum({<Year_month={$(=Year([Year_month]) & '_' &'Jan')}>}Rev)

What did I do wrong? It would be easier later on if I can do it right from the start.

[Anonymous]

Sorry Andrey, somehow I don't see the change in expression.

Can you pls show me here which should be adjusted to make it right? (in the attachment I converted into date format before loading the script)

sum({<Year_month={$(=Year([Year_month]) & '_' &'Jan')}>}Rev)