Using variables inside expressions

[Gonzalo]

Hello to everyone,

This is my first question in this forum, so thank you in advance to all who try to help me ... and sorry about my english, is not good as i'd like 😉

I'm starting to work on qlik sense and i'm a bit confusing (well a lot really) about "set analysis" syntax

I created the following variable with an expression:
let NewYear = IF (Today () <Date (YearStart (Today ()) + 15), 1, 0);

It's working ok, i want to get 1 value when date is between january-1 and january-15 and 0 value in the rest of cases.

I need to use NewYear within an expression like this:

count({$<[OFFER_DATE.autoCalendar.IsLastYear] = {1}, $(NewYear) = {1}>}  + {$< [OFFER_DATE.autoCalendar.IsCurrentYear] = {1}, $(NewYear) = {0}>} OFFER_ID)

This mean, i need to count offers from last year when we are in 1-15 of january period, and get data from current year in the rest of cases.

As you can imagine, this is not working... i have spent a lot of hours searching similar problems within forums and trying different options but without success. What am i doing wrong, or  isn't it possible?

[Anil_Babu_Samineni · Accepted solution]

Create field in script to get that done is the first option (Recommended*)

IF (Today () <Date (YearStart (Today ()) + 15), 1, 0) as NewYear

And expression you can use flag

count({$<[OFFER_DATE.autoCalendar.IsLastYear] = {1}, NewYear = {1}>} + {$< [OFFER_DATE.autoCalendar.IsCurrentYear] = {1}, NewYear = {0}>} OFFER_ID)

Or, If you like with in measure, Perhaps try

count({$<[OFFER_DATE.autoCalendar.IsLastYear] = {1}, Date = {"=IF (Today () <Date (YearStart (Today ()) + 15)"}>} + {$< [OFFER_DATE.autoCalendar.IsCurrentYear] = {1}, Date -= {"=IF (Today () <Date (YearStart (Today ()) + 15)"}>} OFFER_ID)

[Gysbert_Wassenaar]

First of all, in set analysis syntax you can only use field names on the left side of the = operator.

The statement let NewYear = IF (Today () <Date (YearStart (Today ()) + 15), 1, 0); will give the variable NewYear either the value 1 when the real world date is between januari 1st and 15th, the rest of the year the variable NewYear will have the value 0.  Count( {< 1 = 1 >} something) will do nothing because the left 1 is not the name of a field.

This mean, i need to count offers from last year when we are in 1-15 of january period, and get data from current year in the rest of cases.

Do you mean when we (the world) are in 1-15 januari or when the offers have an offer date between 1 and 15 januari?

If the latter then you should create a new field in the load script to flag the order records which have an orderdate between 1 and 15 januari:

Orders:
LOAD OfferId, OFFER_DATE, ..., Other fields, ..., If( DayNumberOfYear(OFFER_DATE) <= 15, 1, 0) as _FlagOfferDateJan1_15
FROM ...source...

You can then use that flag field in the set analysis expression:

count({$<[OFFER_DATE.autoCalendar.IsLastYear] = {1}, _FlagOfferDateJan1_15 = {1}>}  + {$< [OFFER_DATE.autoCalendar.IsCurrentYear] = {1}, _FlagOfferDateJan1_15 = {0}>} OFFER_ID)

[Gonzalo]

ok, only fields can be used on left side of the = operator... understood 🙂

I meant when the world is between 1st and 15th of january, not the offer date.

I've created a new field containg the value calculated, as Anil_Babu_Samineni said

IF (Today () <Date (YearStart (Today ()) + 15), 1, 0) as NewYear

And now expressions are ok and working as expected

Thanks a lot!