Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER 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
MVP & Luminary
MVP & Luminary

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