Using variables in set analysis

[#1]

Hello: I am trying to pull data for same month for the current and the previous years.

Current Fiscal Year and year to date data:
My Expression: Sum for Current year -

=SUM({<FY={$(vMaxFY)},Month={<=$(vPrevMonth)}>}[Volume Received])

Previous Fiscal Year and  year to date data:

My Expression: Sum for Previous year - the bold part is not working. If I remove "<=" it is pulling the last month data.

=SUM({<FY={$(vPriorFY)},Month={<=$(vPrevMonth)}>}[Volume Received])

When I add the "<=" in my formula it is not working. I changed the value to Month Number from Long Name in the variable and still no luck.

Hope my question makes sense 🙂 Any help is greatly appreciated. Thank You.

Best Regards,
Archana

[CELAMBARASAN]

Normally its a best practice in set analysis for string equals use single quotes and for Expression comparison use double quotes

Check the below expression

=SUM({<FY={'$(vPriorFY)'},Month={"<=$(vPrevMonth)"}>}[Volume Received])

[#3]

Thank you Adhimulam.

I tried this ... no luck with this either .. returning zeros.

If I take out <= I am getting last months data. If I want to get the sum of all the previous months (YTD) data using <= it is not working.

[sunny_talwar]

Create a new field in the script like this

LOAD Num(Month(DateField)) as MonthNum,

          DateField,

          Month(DateField) as Month,

          ....

FROM ....

and then try this

=Sum({<FY={$(vPriorFY)},MonthNum={<=$(vPrevMonth)}, Month>}[Volume Received])

Make sure to adjust your vPrevMonth function so that it is using MonthNum field

[satheshreddy]

Hi Archana,

I had a drought in your Exp.

=SUM({<FY={$(vPriorFY)},Month={<=$(vPrevMonth)}>}[Volume Received])

here your variable in this exp it will treate like EXP you have to put Double Cotes like below.

=SUM({<FY={'$(vPriorFY)'},Month={"<=$(vPrevMonth)"}>}[Volume Received])

Regards

Sathish

[#6]

Thanks Much Sunny.
This is returning data. But, am  getting sum for the entire prev Fiscal Year - not just the YTD to compare to current Fiscal years data.

Attached is my report with sample data similar to what I am using.
The FY Comparison table is where my expressions are ... when I select 2017 I get YTD for 2017 and total for FY 2016
Please help ...
THANK YOU!!

[sunny_talwar]

You variable is 12 when I select 2017. What do you want your variable to be when you select 2017? Today's month? January (Month 1)?

[#8]

It will be December. Data will be FYTD thru last month

Example: Using the data attached Fiscal year starts in October.
If I select 2017 - the sum of volume received for FY2017 should be October, November and December and FY2016 data should be for the same period as well

THANKS A MILLION FOR YOUR TIME ON THIS...

Regards,
Archana

[sunny_talwar · Accepted solution]

Oh so you have a fiscal year issue here. You need to create your MonthNum field using a fiscal calendar. To create a fiscal year calendar, look here:

Fiscal and Standard Calendar generation

One you use this calendar to generate a new MonthNum, you should be able to use that in your set analysis

[#10]

Thank you very much Sunny. Will give it a try.