Set Expression - YTD Sales

[#1]

Hi, I am trying to calculate YTD Sales using a set expression, based on the financial month selected.

Our financial year is Jul - Jun.

For Example:

If I select the months Jul, Aug & Sep to get the first quarters YTD sales I should get $3,000,000. If I was to select Oct, Nov & Dec to get the second quarters YTD sales I should get $6,000,000.

Financial Month	Sales
Jul	$1,000,000
Aug	$1,000,000
Sep	$1,000,000
Oct	$1,000,000
Nov	$1,000,000
Dec	$1,000,000
Jan	$1,000,000

I have tried creating a variable called vFinancialMonth and updating it with =GetFieldSelections(FinancialMonth) and then my set expression is:

      sum({$<FinancialMonth = {'>=$(=vFinancialMonth)'}>}Sales)

but this doesn't work.

Does anyone else have any other ideas?

P.S I don't want to have an extra field called Financial Quarter attached to my calendar, I would rather the user just selects the financial months they would like to deal with, this way they could use it for any sort of period they need, eg Quarterly reports, 6 Monthly reports etc.

Thank you very much in advance for your help.

Kind Regards

Jordan

[jagan]

Hi,

If you have a date dimension then try like this

sum({<FinancialYear=, FinancialMonth =, DateDimensionName={'>=$(=YearStart(Today(), 0, 7))<=$(=Date(Max(DateDimensionName)))'}>}Sales)

Note: Replace FinancialYear, FinancialMonth and DateDimensionName fields with your actual dimensions.If this expression not working then attach some sample file.

Regards,

Jagan.

[#3]

Hi Jagan,

Thanks for your reply.

I am unable to attach our actual project and data due to confidentiality, however I have made up a test document to show you what I am trying to do.

When you open the document you will see a Straight Table with a column called "YTD This Year (2015)" if you select the financial year as 2015 and the Financial Month as Sep - Oct, you should have a customer show up called ABC Limited, I want the YTD This Year 2015 column to sum the sales from July (Beginning of the Financial Year) - October (Last month selected), which should be $3,907,000.

I hope this helps explain what we are trying to do.

Thanks

Jordan

[#4 · Accepted solution]

I have worked it out....

I have attached an updated qvw file for anyone to look at and see how I got it going.

I added a month number to my calendar called FinMonthNbr and then used the following expression:

sum({1<FinMonthNbr ={'<=$(=max(FinMonthNbr))'}, FinancialYear = {'$(=getfieldselections(FinancialYear))'}>}Sale)

[jagan]

Hi Jordan,

Adding Quarter to your datamodel will be more helpful, because you don't need to select 3 months to see full Quarter values, just select Quarter.

FinancialMonths:

LOAD * INLINE [

    Month, FinancialMonth, FinMonthNbr, Quarter

    Jul, Jul, 1,Q1

    Aug, Aug, 2,Q1

    Sep, Sep, 3,Q1

    Oct, Oct, 4,Q2

    Nov, Nov, 5,Q2

    Dec, Dec, 6,Q2

    Jan, Jan, 7,Q3

    Feb, Feb, 8,Q3

    Mar, Mar, 9,Q3

    Apr, Apr, 10,Q4

    May, May, 11,Q4

    Jun, Jun, 12,Q4

];

Hope this helps you.

Regards,

Jagan.