topic Sum participants with set expression in App Development

Sum participants with set expression

Woojtek_96 — Mon, 26 Jul 2021 10:11:23 GMT

Hi all,

i have a variable in script:

let vPriorBookingDate = 'date(addyears(SP_BookingDate,-1))'; to make a previous year date and in a straight table

i have dimension SP_Booking date and in expression

Dimension	sum for current date	sum for previous year date
SP_BookingDate ( 2021-07-26)	123	123

I want to with sum({$<SP_BookingDate={"=$(vPriorBookingDate)"}>}SP_ParticipantsCount) sum values in 2020-07-26 but it shows exact same values as in current booking date

is there any possibility to do it correctly?

Thanks for all help and have a nice day!

Re: Sum participants with set expression

bgl-sjoyce — Mon, 26 Jul 2021 17:32:05 GMT

Do not try and use set analysis to set a dimension value for a dimension in a table (SP_BookingDate).

1) You can create a previous year metric in the data model associated to the current date

ex.

left join (factTable)

load

addyears(SP_BookingDate,1) as SP_BookingDate

sum(field) as PreviousYearMetric

resident factTable

;

2) or you can create a field that isn't qualified by year ("DayMonth"), so records for 2021, 2020, 2019 etc can all have the same value for DayMonth, ex. 07-26. Then in your front-end you can use set analysis for Year = $(currentYear) and Year = $(previousYear).