Set Analysis

[pmracek33]

Hello Community,

I have this formula: sum({$<TRANSACTION_MONTH={"$(vYearBack)"}, TRAN_TYPE = {'D'}>} AMOUNT)

It's showing correctly chart of last 12(13) months. But when I select any month on from list box, it shows all the months available in the data. Why? Is there any solution to this?

Thanks!

[vinieme12]

Please post the expression for your variable

VYearBack , this most likely needs tweaking to fix your problem

Can you also post a screenshot of your data model, which table is month being selected from?

[pmracek33]

Here you go: =AddMonths(max(TRANSACTION_MONTH),-12)

[vinieme12]

Is TRANSACTION_MONTH  a plain numerical field? Like 1,2,3 or a dual value field with date representation?

If you subtract 12 from say month=3, addmonths won't work 

You need to use addmonths on datevalues

If it's plain numerical without a date representation it won't work

Use the date field instead, and format the below to match the format of TRANSACTION_MONTH

=AddMonths(max(Date),-12)

[pmracek33]

It is a date, it works fine, just when I select one month from list box, the chart shows all months.

With Clear selection:

After I select any month:

[vinieme12]

What is your dimension field? 

Are you using an AsOF table? How is one transaction month date related to previous 12months data

Can you post a screenshot of your data model?

[pmracek33]

I can't post that, but it's very simple, one table only...

[vinieme12]

 How is one transaction month date related to previous 12months data ?

[pmracek33 · Accepted solution]

I found a solution, it's simple. You were right it was in the variable. Just added set analysis there:

=AddMonths(max({$<TRANSACTION_MONTH=>} TRANSACTION_MONTH),-12)