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: 
MohdRizwan
Contributor
Contributor

Why is my expression not working

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)

2 Replies
Taoufiq_Zarra

@MohdRizwan  can you check

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

gives

Taoufiq_Zarra_0-1642063864719.png

but if i understood correctly you need 202202

?

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
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