Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rhyseretto
Contributor II
Contributor II

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
Partner
Partner

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'?

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
rhyseretto
Contributor II
Contributor II
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
Partner
Partner

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

 

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
rhyseretto
Contributor II
Contributor II
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!