topic Re: Enter variable directly into straight table? in QlikView

Enter variable directly into straight table?

stevelord — Tue, 22 Mar 2016 19:33:34 GMT

Hi, I feel like I saw this demo'd somewhere before but not finding it now. I am setting up a table where the primary dimension would be like week1, week2, week3, week4 where the weeks begin at vStartDate entered by the user. Since I'll be placing this new table into a container with some other graphs, I'd like to have an 'embedded' variable input on the table itself. (But I will survive just putting an input box below the container if putting the input functionality in the table proves overly tricky. )

Calculated dimension might be like (feel free to let me know if something more optimal than this):

if(loggingdate>=vStartDate AND loggingdate<=(vStartDate+6), Dual('Week1',1),

if(loggingdate>(vStartDate+6) AND loggingdate<=(vStartDate+13), Dual('Week2',2),

if(loggingdate>(vStartDate+13) AND loggingdate<=(vStartDate+20), Dual('Week3',3),

if(loggingdate>(vStartDate+20) AND loggingdate<=(vStartDate+27), Dual('Week4',4))))

The expressions will be along the lines of (trusting the calculated dimension to aggregate them correctly by week):

Met Goal: count(distinct if(count(distinct loggingdate)>=4, UserId))

Missed Goal: count(distinct if(count(distinct loggingdate)>0 AND count(distinct loggingdate)<4, UserId))

Logged 0: count(distinct if(count(distinct loggingdate)=0, UserId))

Oh yeah, would a line chart work with exactly the same setup? Say have week 1,2 ,3, 4 on the bottom and a few lines zipping along? (Suppose I'll find that out once I build it and just toggle graph types for kicks. )

Thanks in advance!

Re: Enter variable directly into straight table?

swuehl — Tue, 22 Mar 2016 19:46:28 GMT

I think you would need to place an input box in your container, at least I can't really think of creating a single variable input within the chart itself (first thought was using input fields, but this was before I understood what you want to achieve).

Regarding your expressions:

count(distinct if(count(distinct loggingdate)>=4, UserId))

is embedding an aggregation function into another aggregation, which in general does not work. You probably would need advanced aggregation here.

Regards,

Stefan

Re: Enter variable directly into straight table?

stevelord — Tue, 22 Mar 2016 20:40:10 GMT

Thanks, and you're right about the expression too. It's not breaking out on the calculated dimension at all, and now I'm not managing to get the calculated dimension parts into the aggr() part of the expression.

My calculated dimension looks okay when I just put 1 as the expression to test it. Just a bear of a time trying to count the number of users who logged x number of days within the various date ranges of the calculated week dimension. Presently, end users can select each 7-day period on a list box, and transcribe or export/paste week's information one at a time, which gets tedious around week 4 5 and 6 of any given event. (Though a far sight better than the export/countif/vlookups of olden times. )

Re: Enter variable directly into straight table?

stevelord — Tue, 22 Mar 2016 22:24:07 GMT

And yes, I agree about advanced aggregation too.

Thinking now I need to make a master calendar..

Then something on the challenges table to make a week column that labels dates week1, week2, etcetera based on calculations around challengestartdate values grouped by challenge id... (er.. calendar script inside that table even?!)

Then link the challenge dates and their corresponding week labels to the master calendar on one side..

Then (ugh) link the users' loggingdates to the master calendar.

to make the most hideous table like

mcdate, challengedates, challengeweek, challengeid, userid, userloggingdate

Then a chart on the front:

dimension: challengeweek

expression: count(distinct if(aggr(count(distinct loggingdate),userid,challengeweek)>=4,UserId))

That count(distinct if(aggr(count(Distinct ),)) bit actually works okay in other kinds of tables. I think because everything in the if(aggr()) part is isolated and calculated first and maybe the outside count(distinct ) is only seeing the resulting list of userids who had the if(aggr(count())) >=4. It only gets lost when you try to bump it up against a calculated dimension. .. or try to put the calculated dimension's expression directly in it...

I think I got away with other tables like this (breakouts by week or month) if I was working with dates internal to the one table, or actual calendar months/years that could be extracted from the loggingdates. What I'm doing now feels like dealing with multiple calendars. 😕

Re: Enter variable directly into straight table?

stevelord — Tue, 22 Mar 2016 22:40:56 GMT

And actually, if that outside count(distinct ) can only see the list of user ids, then it makes sense the calculated dimension itself can't do anything - it's only seeing the resulting counts from that outside count(distinct ).

And efforts to get the date ranges into that expression were ineffective every way I tried. Seems likely the only way to go will be for me to build up some big omni-table in script. And maybe do all the counting functions one temptable at a time in script from there...

Some hours of my life I'd like back now.