Show Last Year's Sales to Date when selecting a Fiscal Year

[rhyseretto]

Data looks like this:

	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov
2019-2020	$4	$3	$5	$5	$5	$3	$6	$4	$2	$6	$3	$6
2020-2021	$5	$7	-	-	-	-	-	-	-	-	-	-

User selects Fiscal Year 2020-2021. The expression should then sum the sales for Dec & Jan of Fiscal Year 2019-2020, and return '$7'.

My (failed) expression currently reads:

<!--
if(Sum({<[Fiscal_Month]>}Sales)>0,
Sum({<[Fiscal_Year]={'$(v_FiscalPrevYear)'}>}Sales))
-->

Probably missing something small and simple! v_FiscalPrevYear simply stores the value of the prev fiscal year, based on fiscal year selection.

Thanks!

[Vegar]

What selections are you using if? If you do other calendar selections than Fiscal Year then you should probably cancel these in your set like this.

if(Sum({<[Fiscal_Month]>}Sales)>0,
Sum({<Year, Fiscal_Month, [Fiscal_Year]={'$(v_FiscalPrevYear)'}>}Sales))

Can you verify that your expression is not working when replacing the variable v_FiscalPrevYear with the actual string value '2020-2021'?

[rhyseretto]

@Vegar 

No other calendar selections are made - only (1) Fiscal_Year. The expression in my original post, and in your response both give the same value; the sum of FiscalPrevYear (all months).

I've also tried replacing v_FiscalPrevYear with '2019-2020' and it still adds all 12 months rather than the 2 months I wish it to.

[Vegar]

Consider this script;

TMP:
CrossTable('Month','Sales')
LOAD * inline [
Year,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov
2019-2020,	4,	3,	5,	5,	5,	3,	6,	4,	2,	6,	3,	6
2020-2021,	5,	7];
Final:
NoConcatenate
LOAD Year as Fiscal_Year, month(date#(Month, 'MMM')) as Fiscal_Month, money(Sales) as Sales
Resident TMP;

DROP TABLE TMP;
LET v_FiscalPrevYear = '2020-2021';

When I use this expression:

if( Sum({<[Fiscal_Month]>}Sales)>0,
Sum({<[Fiscal_Year]={'$(v_FiscalPrevYear)'}>}Sales)
)

I get the following output. Is this as you desire or what is the expected output for this data set and expression?

[rhyseretto]

@Vegar 

Expected output is that if the user makes a selection of Fiscal_Year = 2020-2021, the output is the sum of sales for Fiscal_Year 2019-2020 WHERE Fiscal_Month sales of 2020-2021 > 0. In this case it would be $7 because;

User's selection = 2020-2021

Fiscal_Month where Sales>0; Dec ($5) and Jan ($7)

So; Sum Dec2019-2020 ($4) and Jan2019-2020 ($3)

Output: $7

Sorry if I'm not explaining clearly. Thank you for your patience!