Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rhyseretto
Contributor III
Contributor III

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

Data looks like this:

 DecJanFebMarAprMayJunJulAugSepOctNov
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!

Labels (4)
4 Replies
Vegar
MVP
MVP

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
Contributor III
Contributor III
Author

@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
MVP
MVP

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?

Vegar_1-1614935322575.png

 

 

 

rhyseretto
Contributor III
Contributor III
Author

@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!