topic Re: Looping / selecting range in expression in QlikView

Looping / selecting range in expression

jag7777777 — Mon, 01 Dec 2014 11:27:10 GMT

Hi,

Upon start-up, I'm using actions to select various values - such as current year, last year etc.. so I can initialise our dashboard to show a year on year comparison.

I have a list box which contains week numbers - and due to not wanting to include incomplete weeks in our comparison - I'd like to select all weeks up to the previous week.

I've already created a data island in the script to get variables for current weeks, days, years etc... so using 'current week - 1' is fine to get the cut-off point. The tricky part is looping through from (week 1) to (current week - 1) as the selection.

If I could use a while loop it would be simple...or some sort of range logic like:

= 1 .. ([Current Week] -1)

Any help appreciated,

Regards,

John

Re: Looping / selecting range in expression

sushil353 — Mon, 01 Dec 2014 11:29:00 GMT

Hi,

Can you post some sample data and the expression that u r using.

Re: Looping / selecting range in expression

jag7777777 — Mon, 01 Dec 2014 11:32:53 GMT

Hi Sushil,

No sample data needed...?

It's a field called Week that contains numbers (1 to 53) - and there's a variable called Current Week.

I need to auto-select Weeks 1 to [Current Week] - 1. Pretty simple I know, but there's no looping syntax in the expression.

Cheers,

John

Re: Looping / selecting range in expression

tresB — Mon, 01 Dec 2014 11:37:09 GMT

Search string, something like:

='('&Concat(DISTINCT if(Week<$(CurrentWeek),Week), '|')&')'

Re: Looping / selecting range in expression

jag7777777 — Mon, 01 Dec 2014 12:55:07 GMT

Almost there thanks tresesco....

This seems to miss the first week, but does get all the rest?

Re: Looping / selecting range in expression

jag7777777 — Fri, 05 Dec 2014 09:13:18 GMT

Hi Tresesco,

This did work!

We wrote the same code again (from memory whilst on the train to London) in a copy of our UI qvw and it worked perfectly.

When we got back, we checked the code in the original UI qvw and it was the same - yet still didn't return the first week..?

Turns out QlivView was remembering our selection!!! So missing the first week had nothing to do with the syntax!

Anyway, a clear all and save - then re-open is something I'll never forget again!

Cheers Tresesco for the solution 🙂