Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Using variables inside expressions

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?

Labels (1)
1 Solution

Accepted Solutions

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

3 Replies

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

MVP & Luminary
MVP & Luminary

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)

 


talk is cheap, supply exceeds demand
Contributor
Contributor

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!