Last Month Where Data and Date/Text Issue

[Anonymous]

Dear all,

I have been battling with this problem for a few days and I suspect there is a simple solution.

I have a list of account details with revenue by month but the month data goes to end of 2017 and with 0 where in the future. The current month of data is APR-17 and would not always be the current month or current month-1. I ultimately want to get to a solution where I can do a sum of the revenue for current month versus last month so I can sort it to show largest changes month on month.

I have been trying to create month on month analysis by isolating the current max month where there is data (see variable below) and using the variable as the input for the set analysis (see formula).

I have tried many variations but cannot get the set analysis to accept the variable result. I think it has something to do with the format of the cell but even in varying the solutions around this I get no result, it will only accept if I hard code "APR-2017" as the value.

Variable (MaxMonth)

=AGGR(max({<FACT_MONTHYEAR_DATE={"=sum(AMOUNT)>0"}>} date#(FACT_MONTHYEAR_DATE,'MMM-YYYY')),CLIENT_KEY)

Formula

AGGR(SUM({$<FACT_MONTHYEAR_DATE={$(=MaxMonth)}>}AMOUNT),FACT_MONTHYEAR_DATE)

I have attached my excel source data and a QVD I have been working on. Thanks in advance for any suggestions.

Ben

[sunny_talwar · Accepted solution]

There were few issues

1) Variable MaxMonth was not created (might have been missed in the sample)

2) MonthYear field was not read as date, but as text

3) some other minor changes

PFA

[Anonymous]

Sunny - many thanks, it worked!!

Seems I was close to the solution, and looks like the difference mainly was the setup of the variable in the set analysis.

I researched the syntax on this site before and came across:

SUM({$<FACT_MONTHYEAR_DATE={$(=vMaxMonth)}>}AMOUNT)

but you have corrected this to:

SUM({$<FACT_MONTHYEAR_DATE={"$(=vMaxMonth)"}>}AMOUNT)

i.e. inclusion of the speech marks. What is the effect of the speech mark inclusion (aside from making it work!)

[sunny_talwar]

I will give you links which will help you understand this better

A Primer on Set Analysis‌

‌Dates in Set Analysis