topic Dynamic Sheet in QlikView

Dynamic Sheet

pgalvezt — Mon, 28 Mar 2011 19:38:56 GMT

Hi all,

I need some help with this. I have 5 Sheets on my Qlikview document and what I want is to change each sheet each 30 seconds, Can you help me with this. I tried with the example here in the qlikview web (Dynamic Sheets = the sheet turn every 10 seconds), but I cant get results. If Anybody can help me with this... Thanks in advance...

Dynamic Sheet

pljsoftware — Tue, 29 Mar 2011 09:23:20 GMT

Hola Pablo,

can you explain how you want to change each sheet each 30 seconds? Can you post the link about qlikview web dynamic sheets that turn every 10 seconds?

Saludos
Luca

Dynamic Sheet

Tue, 29 Mar 2011 09:28:48 GMT

Hi Pablo,

This sounds like only a macro can achieve this. I don't know of anyhow native functionality or parameter allowing dynamic change of sheets based on time. It would be a nice future feature in QV.

Hopefully someone has done this with macros. I also have a client that is interested in this feature for an LCD display of QlikView on a shop floor.

Dynamic Sheet

jonathandienst — Tue, 29 Mar 2011 09:46:40 GMT

Pablo

Do you mean that you want the model to cycle through the five screens, changing every 30 seconds - for a large screen display (in for example a call centre)?

If so, you can use this approach:

Create a variable vShow with the following value:

=ceil((frac(now())*86400/(vSheets*vDelay)-floor(frac(now())*86400/(vSheets*vDelay)))*(vSheets))

Create variable vSheets = no of sheets to cycle through (5 in this case)
Create variable vDelay = time in seconds (30 in your case)
Finally, set each sheet to conditionally display using the expression

vShow=1 (for the first sheet)
vShow=2 (for the second sheet, and so on)

The vShow variable expression will return the value 1, then after 30 seconds, the value 2 etc, causing the displayed sheet to change every 30 seconds.

Hope that helps

Jonathan

Dynamic Sheet

Tue, 29 Mar 2011 09:53:35 GMT

Hi Jonathan,

Superb. Thank you for that methodology.

Dynamic Sheet

pgalvezt — Tue, 29 Mar 2011 13:41:00 GMT

First of all Thank you all for your answers.

I'm Going to use the macro that you indicated Jonathan. And I will comment you the results. Thanks

I Attached the example with dynamycs sheets. The idea is to change the time in the sheets. The division I made by 30. But just took 2 sheets. It must be because the value on function Floor must be changed. But I can´t get possitive results.

Thanks Again

Dynamic Sheet

Anonymous — Tue, 29 Mar 2011 15:34:37 GMT

Pablo,
I recommended avoiding function now(), except in the load script.
There is an alternative way which I used in QV 7x (didn't have requirements like that later). Two variables:
Rotations: Number of cycles (gives your flexibility, and very useful for testing)
Delay: Delay on sheet, in seconds
The macro is approximately like this:


sub Rotate
 on error resume next
 set r=ActiveDocument.Variables("Rotations").GetContent
 set d=ActiveDocument.Variables("Delay").GetContent
 d=d.string*1000
 for i = 1 to r.string
 ActiveDocument.Sheets("Sheet 1 name").Activate
 ActiveDocument.GetApplication.WaitForIdle
 ActiveDocument.GetApplication.Sleep d
 ActiveDocument.Sheets("Sheet 2 name").Activate
 ActiveDocument.GetApplication.WaitForIdle
 ActiveDocument.GetApplication.Sleep d
 ActiveDocument.Sheets("Sheet 3 name").Activate
 ActiveDocument.GetApplication.WaitForIdle
 ActiveDocument.GetApplication.Sleep d
 next
end sub

I attached this macro to a button on a separate configuration tab. Input box for the variables was right there, next to the button.
Not sure how it works in QV9 or 10. You can give it a try.

Dynamic Sheet

johnw — Tue, 29 Mar 2011 15:43:26 GMT

The attached example is a little more complicated than you need, in that different tabs stay active for different amounts of time. It uses no macro code or actions, just display conditions for each tab sensitive to the current time. Tested in v9 and v10.

Michael, I consider it safe to use now() in this case. Now() polls the operating system once per second. If you're only doing that once per second per tab, that should be a pretty trivial amount of CPU. You have to be very careful with how you use now(), but there are cases where I think it is appropriate. This is one of those cases.

(Edit: It looks like this is almost the same as Jonathan's solution. Maybe I can combine them in an even more efficient way since he only checks now() once per second, not once per second per tab. Hmmm.)

Dynamic Sheet

pgalvezt — Tue, 29 Mar 2011 19:18:03 GMT

Thanks John that is exactly what I needed. I just have to change the values on the inline table to 30 seconds.

Thank you all.

Dynamic Sheet

johnw — Tue, 29 Mar 2011 20:52:50 GMT

OK, I think I've integrated Jonathan's approach into my example, but still allowing for different timing for each sheet. I believe now() will only be evaluated once per second this way. We generate variables from the inline table, but don't actually store the data, so it won't interfere with the data model in any way.

Here's the script:

[Timing]:
LOAD
recno() as Tab
,rangesum(Seconds,peek(Seconds)) as Seconds
INLINE [
Seconds
20
10
5
];

[Show]:
LOAD
'=' & concat('if(vSeconds<' & Seconds & ',' & Tab,'
,') & repeat(')',max(Tab)) as Show
,'=mod(round((now()-now(2))*86400),' & max(Seconds) & ')' as Seconds
RESIDENT Timing;
LET vShow = peek('Show');
LET vSeconds = peek('Seconds');
DROP TABLES [Show], [Timing];

Dynamic Sheet

pgalvezt — Wed, 30 Mar 2011 13:31:39 GMT

It is a very good exercise and thank all of you.

Re: Dynamic Sheet

Thu, 15 Nov 2012 14:24:56 GMT

Gentlmen,

Thank you for the great information. I am running into an issue with my tv dashboard display auto switching between tabs. The auto switching works in the desktop client but I am unable to get the tabs to switch in a browser.

If I click the browser screen, the tab will count down and switch if I keep clicking but it will not switch on its own. I am running my dashboard in a Chrome browser on a large tv screen. If I run the dashboard in Internet Explorer it stil does not work and requires me to continuously click the screen for it to switch tabs.

Can someine provide some guidance?

Thanks,

Rob

Re: Dynamic Sheet

Anonymous — Thu, 28 Mar 2013 09:45:21 GMT

I copied the C:\Program Files\QlikView\Server\QlikViewClients\QlikViewAjax\opendoc.htm file and called it OpenDocNoToolbar.htm.

I then replaced the HTML with the following...

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

</head>

<span class="ctx-menu-text">Close</span>

</a>

</li>

</ul>

<span class="ctx-menu-text">Loading...</span>

</div>

</div>

var qva;

function Init() {

var doc = Qva.ExtractProperty("document", null);

var re = new RegExp("^(//[^:/]+(?::\\d+)?/)?([^#]+)");

var segm = re.exec(doc);

doc = segm && segm[2];

var invite = Qva.ExtractProperty("invite", null);

if(invite) doc = "";

if(doc != null) {

qva = new Qva.PageBinding();

qva.View = doc;

qva.Autoview = "";

qva.ShowDocumentTitle = true;

qva.AuthenticateUrl = '/QvAjaxZfc/Authenticate.aspx';

new Qva.Modal();

new Qva.Scanner(qva);

qva.AutoViewAppend (null, qva.DefaultScope, 'servercontrol');

new Qva.Collaboration(qva, document.getElementById("MainContainer"));

Qva.DragDrop.DropDefault = {

'Inside': function(pos, type) {

if (type == 'Field' || type == 'Dimension' || type == 'ObjectType') {

return { 'Element': 'Document.ActiveSheet' };

} else if (type == 'Object') {

return { 'Element': 'Document.ActiveSheet', 'Dialog': 'Drop.qvpp' };

} else {

return null;

}

};

new Qva.Extensions(qva);

qva.AddManager({

'Name': qva.DefaultScope + ".Modal",

'Paint': function (mode, node) {

var width = parseInt(node.getAttribute('width'));

var height = parseInt(node.getAttribute('height'));

var name = node.getAttribute('value');

var url = Qva.Remote + '?public=only&name=' + escape(name) + '.htm';

var params = node.getElementsByTagName("param");

for(var i = 0; i < params.length; ++i) {

var key = params.getAttribute("key");

var value = params.getAttribute("value");

url += "&" + escape(key) + "=" + escape(value);

}

Qva.Modal.instance.Show(qva, url, width, height, true, "QvModal_SessionCollaboration");

}

});

qva._Send("add", "Document.ActiveSheet", false);

} else {

alert("No Document");

}

Qva.SetContextMenu (document, function(event) { return qva.OnContextMenu(event); });

Qva.Start();

}

function CloseSession() {

document.body.style.background = "";

$('#MainContainer, #Tabrow, #QvAjaxToolbar > ul:last').html("");

$('body > script').nextAll().remove();

qva.Enabled = false;

qva.Ready = function () {};

qva.OnContextMenu = function () {};

qva.SendClose();

var $closebtn = $('#Close');

if ($closebtn.length) {

$closebtn.find('.ctx-menu-text').text($closebtn[0].RestoreText || "Restore").end().show()[0].href = 'javascript:RestoreSession()';

}

function RestoreSession() {

window.location = window.location;

}

function Assert(exp, message) {

if (!exp) {

if(message) { alert(message); }

debugger;

}

return exp;

}

if (typeof(Qva.Mgr) === "undefined") {

Qva.GetDebugFiles(Init);

} else { Init(); }

</script>

</body>

</html>

Then for the Document/s you want to remove the tool bar add the URL as below -

When you access the document through the client browser you should see the OpenDocNoToolbar.htm is in the URL.

By doing this it removed the Tool Bar and Close button.

I also wanted my Document to automatically scroll through each sheet every minute, as the Document was to be shown on a TV screen in the office. You can do this using the Show Sheet Conditional on the Sheet Properties.

Create a variable called vShow and then use an expression to give the variable a value. For instance, my document has 5 sheets and I wanted to show each sheet for 1 minute, so my expression looked like this...

=if(right(time( now(),'m'),1)=1 or right(time( now(),'m'),1)=6 ,1,

if(right(time( now(),'m'),1)=2 or right(time( now(),'m'),1)=7 ,2,

if(right(time( now(),'m'),1)=3 or right(time( now(),'m'),1)=8 ,3,

if(right(time( now(),'m'),1)=4 or right(time( now(),'m'),1)=9 ,4,

if(right(time( now(),'m'),1)=0 or right(time( now(),'m'),1)=5 ,5)))))

So my vShow value would either be 1,2,3,4 or 5 depending on the current minute. You can use any expresion that suits the number of sheets you have and the frequency you want to show them.

For each sheet then update the Conditional value i.e. Sheet 1 would be vShow=1, sheet 2 vShow=2 etc

Now everyminute the sheet should change automatically. If you need to make changes to sheets just change the vShow expression to the sheet number you want to see - this saves you having to wait for the document to go through all the sheets.

For me the browser didn't automatically scroll through each sheet, so I installed Easy Auto Refresh for Chrome which lets you specify how many seconds between each browser refresh. Now I can run the browser in full-screen mode, sit back and watch it scroll through each sheet every minute.

Re: Dynamic Sheet

Tue, 29 Oct 2013 12:45:38 GMT

Hello Jonathan,

I am having two buttons on the welcome page "ManualMode" and "SlideShow"
I am using the same concept which you have shown. The problem I am facing is whatever the value of vShow is at the time I click SlideShow it goes to that page. For eg if the value of the vShow variable was 4 when I clicked Slideshow button it goes to the 4th tab. Infact I want to always start with the 2nd tab when I click on slideShow.

Please Help....

Re: Dynamic Sheet

mr_ramshini — Sat, 07 Mar 2015 07:23:27 GMT

I want switch between pages automatically

But i dont know do

Please help me

Re: Dynamic Sheet

mr_ramshini — Sat, 07 Mar 2015 07:41:59 GMT

I have 3 sheets in qlik view

and i want switch automatically between sheets

please help me

thanks

Re: Dynamic Sheet

mr_ramshini — Sun, 08 Mar 2015 07:20:25 GMT

I have a problem

I have a project with 3 sheets and i want show sheets after together automatically

I want show sheet1 after 2 second show sheet2

But i do'nt know do it

Please Help Me

Re: Dynamic Sheet

mr_ramshini — Sun, 08 Mar 2015 09:17:58 GMT

I have a project in qlik view that have a 3 sheet

and i want go of sheet1 to sheet2 automatically after 2 second

please help me

Thanks

Re: Dynamic Sheet

Tue, 05 May 2015 06:46:39 GMT

Hi,

I have the dynamic sheet solution that is described by Jonathan above. Unfortunately this solution leads to that the nonactive sheets are not visible in the tabrow. I have like 10 sheets to rotate and from time to time i want to manually activate one of them. Is that somehow possible? Thanks!

Re: Dynamic Sheet

mohammadkhatimi — Wed, 06 May 2015 07:25:53 GMT

Hie Jonathan,

Its not working on Accesspoint

Kindly give the solution for working on Accesspoint

Regards,

Mohammad