topic Why is my expression not working in QlikView

Why is my expression not working

MohdRizwan — Thu, 13 Jan 2022 07:56:48 GMT

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)

Re: Why is my expression not working

Taoufiq_Zarra — Thu, 13 Jan 2022 08:51:35 GMT

@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

Re: Why is my expression not working

marcus_sommer — Thu, 13 Jan 2022 09:02:35 GMT

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