Why is my expression not working

[MohdRizwan]

Hi,

Please help me why my code is not working on expressions

Below is my code on expressions 

=sum(if(om_daily.txn_week = Num(year(Today(1)-1)&Right(concat('00'&(Week(Today(1)-1)-1)),2)) and om_daily.metric_type = 'WEEKLY',om_daily.active_mx)/1000)

Below code working fine when i am giving direct condition for YYYYWW

=sum(if(om_daily.txn_week = '202202' and om_daily.metric_type = 'WEEKLY',om_daily.active_mx)/1000)

[Taoufiq_Zarra]

@MohdRizwan  can you check

=Num(year(Today(1)-1)&Right(concat('00'&(Week(Today(1)-1)-1)),2)) 

gives

but if i understood correctly you need 202202

?

[marcus_sommer]

There are multiple issues. Your combining of numbers with & returned a string and the same happens if  you applies string-functions like right() - which num() couldn't format. Further you used here concat() which is an aggregation-function and not designed to combine strings. Therefore you may try it in this way:

sum({< om_daily.txn_week = {"$(=date(today(), 'YYYYWW'))"}, 
               om_daily.metric_type = {'WEEKLY'}>} om_daily.active_mx) / 1000

which simplifies the approach to create the right year-week and used set analysis instead an if-loop which is much more flexible and performant. Depending on your used week-number-system (starting when with which number ...) you may need some adjustment to add 1 or -1 or you tries it with another function like: 

replace(weekname(today()), '/', '').

I suggest just to play with the week-expressions within a separate textbox to see which one returned which result.

- Marcus