Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Dynamic Set Analysis From Inline Table Values

Hi,

I am trying to create a straight table that will show me a bunch of calculations where each row represents a different time period (as seen in the attached file), so the only difference is the Date dimension.

For this, I have created the following inline table:

Periods_Menu:
NoConcatenate
LOAD * INLINE [
%Period_Desc, %Period_Date_Diff, %Period_Sort_Val
Yesterday, -1, 1
Last 7 Days, -7, 2
Last 30 Days, -30, 3
Last 90 Days, -90, 4
]
;

I have a field in my calendar called 'Date Diff' which represents the difference in days from today, so the date of a week ago will have the value of -7, the date of yesterday will have the value of -1, today will have the value of 0 etc.

What I am trying to do is set the 'Date Diff' value to be greater than the value that is shown under the %Period_Date_Diff field in my inline table, but without success.

I tried using the only() function but that happens to work only if one period is selected. I also tried using the 'Pick' and 'Match' functions, but still without any success.

Here is the expression I tried using:

pick(Match(%Period_Desc, $(=chr(39)&concat(%Period_Desc, chr(39)&','&chr(39),%Period_Sort_Val)&chr(39))),

$(=Concat(%Period_Date_Diff, ',',%Period_Sort_Val)))

this does give me the right value of %Period_Date_Diff for each row in the table, but doesn't work when trying to put this as a set modifier.

Can anyone help me out with this?

Thanks!

4 Replies
swuehl
Not applicable

Re: Dynamic Set Analysis From Inline Table Values

A set expression is only evaluated once per chart, not per dimension value, so you can't create a set expression that is responsive to your row's dimension value.

But as you already approached to, you can use a pick / match combination, but you will need to enter several expressions with set analysis into the branches, something like (simplified):

=pick ( match( %Period_Desc, 'Yesterday', 'Last 7 Days')

,sum({<[Date Diff] = {">=-1"}>} Value)

,sum({<[Date Diff] = {">=-7"}>} Value)

)

You can try to create this expression as variable in your script, using your table as input, but that's another story.

Not applicable

Re: Dynamic Set Analysis From Inline Table Values

Thanks a lot for the reply!

I see what you are saying, the problem is I have quite a lot expressions to manage, so I figured it'll be easier to handle it using this set analysis option. But, according to your reply, I guess this isn't possible.

Do you have any recommended way of applying this?

Thanks,

Yaniv

jontydkpi
Not applicable

Re: Dynamic Set Analysis From Inline Table Values

Hi

If you are using a master calendar, you can assign a set of flag fields in the calendar to indicate the date perdiods of interest to you. Something like this in the master calendar load:

     LOAD date As Date,

          month(date) As Month,

          ...

          If(date = Today() - 1, 1, 0) As IsYesterday,

          If(date > Today() - 7, 1, 0) As IsLast7Days,

          If(date > Today() - 90, 1, 0) As IsLast90Days,

          ...

Now you can use the flag fields in a sum(if()) or set expression to select the dates in the period of interest.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
stabben23
Not applicable

Re: Dynamic Set Analysis From Inline Table Values

Hi,

You can add a field in your inline like this:

Periods_Menu:

LOAD * INLINE [

%Period_Desc, %Period_Date_Diff_Start, %Period_Date_Diff_End

Yesterday, -1,  -1

Last 7 Days, -7, -2

Last 30 Days, -30, -8

Last 90 Days, -90, -31 ];

you can now do a intervallmatch on you "DateDiff" field and get all diff values to match your %Period_Desc

left Join IntervalMatch(DateDiff) LOAD %Period_Date_Diff_Start, %Period_Date_Diff_End RESIDENT Periods_Menu;