Set Analysis Calculation Help

[#1]

Hi guys,

I am hoping someone can help me out with this.

I have a requirement to produce a graph that shows a current month value divided by previous month's value.

This is possible to achieve, but I am now asked to show this trend over time.

I have come up with this expression and I have a time dimension which I use in the chart to display all the prior months and also the current month used in the application.

=Sum(
{$<[APP_DATE]= {'$(=(max(date(APP_DATE))))'}>}
AMOUNT)

/

=Sum(
{$<[APP_DATE]= {'$(=date(addmonths(date(max(APP_DATE),'MMM-YYYY'),-1),'MMM-YYYY'))'}>}
AMOUNT)

My expression works, but only gives one value, which is for the most recent month/previous month.

How can I modify my expression to show the trend over time?

Any help on this will be greatly appreciated

Thank you

[Oleg_Troyansky]

Unfortunately, this is not something you can do with Set Analysis, because the Set Analysis expression is being evaluated once for the whole chart, and not at every cell. Therefore, it cannot be sensitive to the specific values of chart dimensions.

In order to create this type of chart, you'll have to add another table to your data model, with one more Date field (we usually call it "As of Date" ) and a defined relation between your "Transaction Date" and "As of Date". In this table, you can define flags for "Current Month", "Prior Month" or any other relations you might need. The flags are assigned 1 if the Transaction Date satisfies a condition, compared to "As of Date".

This solution has been described in detail numerous times on this forum, please search for it.

cheers,

[#3]

I'm searching for this solution in the forum but can't quite get the right search words.  Would you be able to point me in the right direction? Thanks.

[johnw]

Try searching for AsOf, no space.