Year selection in list box not showing result in text object.

[vikasshana]

Hi,

I do have below field in my app.

Period	Sales
2021006	100
2021005	200
2020011	300
2020009	400

In order to extract year, current year and pervious year from Period field I've created below expressions and stored in a variable.

vPeriod=Year(Date#(Period,'YYYYMM'))

vPeriodCurrent=(Num(Year(today(vPeriod))))

vPeriodLast=(Num(Year(today(vPeriod))-1))

So now I'm using the below formulae to get pervious year and current year sales when user selects year from list box, but it is displaying '0' when ever user selects year.

=if(vPeriod=vPeriodCurrent,Sales,0)

Regards,

Vikas

[stevejoyce]

I would recommend you evaluate your variables by themselves and see if they are returning the results you want.

Couple things that are an issue:  Selecting Year of Period is going to result in multiple period values, so this wouldn't work and isn't what you are trying to do.

To make it easier, add Year in your data model, so you can eliminate your listbox being a calulated field and be able to use set analysis.

-> either add in your initial data load when loading period/sales, or join in

left join (table)
load
Period,
Year(Date#(Period, 'YYYYMM'))as Year
resident table;

now your variables, can be set in load script but this will take into account user selections:

set vMaxYear = =max(Year);

set vPreviousYear = =max(Year)-1;

Now your KPI calculation is: sum({<Year = {$(vMaxYear)} >}Sales)