topic Re: How to add constraint to Cyclic Group? in QlikView

How to add constraint to Cyclic Group?

Anonymous — Tue, 03 Oct 2017 17:17:15 GMT

I have one graph with three different views depending on how it's cycled through:

Hour (EST):

Expression- =Time(ConvertToLocalTime(Hour,'GMT+00:00'),'hh:mm')

Hour(IST):

Expression-=Time(ConvertToLocalTime(Hour,'GMT+09:30'),'hh:mm')

Hour (GMT):

Expression- =Time(ConvertToLocalTime(Hour,'GMT+04:00'),'hh:mm')

I need the graphs to show the following:

US working hour (EST) : Showing the graph for US working hours (7:00am EST to 5:00pm EST)
India working hour (IST): Showing the graph for India working hours (9:00am IST to 6:30pm IST)
EMEA working hours (GMT): Showing the graph for EMEA working hours (8:00am GMT to 6:00pm GMT)

How can I enable these constraints?

Re: How to add constraint to Cyclic Group?

marcus_sommer — Wed, 04 Oct 2017 15:42:39 GMT

I think you could use dual() to effect the sorting. Maybe with something like:

dual(

Time(ConvertToLocalTime(Hour,'GMT+00:00'),'hh:mm'),

hour((ConvertToLocalTime(Hour,'GMT+00:00')) + 7)

- Marcus

Re: How to add constraint to Cyclic Group?

Anonymous — Wed, 04 Oct 2017 17:25:44 GMT

Hmm that doesn't seem to work for me.

Re: How to add constraint to Cyclic Group?

marcus_sommer — Wed, 04 Oct 2017 20:56:11 GMT

Please provide a small example.

- Marcus

Re: How to add constraint to Cyclic Group?

SergeyMak — Thu, 05 Oct 2017 10:55:28 GMT

Hi Karly,

I'd suggest creating three different filed in load script and also use If statement to calc only the range you need. For Example IF(Hour>=7and Hour <=17, Time(ConvertToLocalTime(Hour,'GMT+00:00'),'hh:mm'),Null()) AS TimeEST etc.

Then use those new fields in your Cyclic Group just tick the box to hide Nulls

You can even limit it just based on your original values of Hour field, but just convert when you need it.

Regards,

Sergey

Re: How to add constraint to Cyclic Group?

Anonymous — Thu, 05 Oct 2017 12:54:30 GMT

Hi Marcus,

I can provide the data from the hour field:

I have it loaded in the script as-

Time(if(isNUM(Hour), Hour, Time#(Hour, 'hh:mm')), 'hh:mm') as Hour

Re: How to add constraint to Cyclic Group?

marcus_sommer — Thu, 05 Oct 2017 13:41:26 GMT

I didn't meant an adjusting within the script else within the calculated dimensions which are in use in your dimension-group. The numeric part of the dual-function is intended to manipulate the sorting whereby I now see that my suggestion was a bit to short and contained a syntax-error through an extra bracket. Therefore try this:

dual(

Time(ConvertToLocalTime(Hour,'GMT+00:00'),'hh:mm'),

if(hour(ConvertToLocalTime(Hour,'GMT+00:00')) < 7,

hour(ConvertToLocalTime(Hour,'GMT+00:00')) + 24,

hour(ConvertToLocalTime(Hour,'GMT+00:00'))))

- Marcus

Re: How to add constraint to Cyclic Group?

Anonymous — Thu, 05 Oct 2017 16:48:34 GMT

That doesn't seem to change anything either.

Re: How to add constraint to Cyclic Group?

marcus_sommer — Fri, 06 Oct 2017 06:40:57 GMT

Please provide a small sample-qvw.

- Marcus

Re: How to add constraint to Cyclic Group?

Anonymous — Fri, 06 Oct 2017 12:40:32 GMT

Here you go:

Re: How to add constraint to Cyclic Group?

Anonymous — Fri, 06 Oct 2017 12:40:54 GMT

I wasn't able to get that to work for me.

Re: How to add constraint to Cyclic Group?

marcus_sommer — Fri, 06 Oct 2017 14:18:31 GMT

I did misunderstand what you want to do - not sorting was the issues you only wanted to display some hours and not all. This could be done with some if-condition like mentioned by Sergey.

But there is another issue within your excel-loading - without using a rounding-function you will get different fieldvalues for most of the numeric- and string-values. Within the app I replaced it with an autogenerate-load but your excel-loading should be look like (you might change the time-format again):

....

LOAD time(If(IsNum(Hour), floor(Hour, 1/24/60/60), floor(Time#(Hour, 'hh:mm'), 1/24/60/60)), 'hh:mm') as Hour

....

- Marcus

Re: How to add constraint to Cyclic Group?

pradosh_thakur — Fri, 06 Oct 2017 15:23:21 GMT

A small correction all the time comes mostly in GMT so it should be like.

US working hour (EST) : Showing the graph for US working hours (7:00am EST to 5:00pm EST) GMT-04:30
India working hour (IST): Showing the graph for India working hours (9:00am IST to 6:30pm IST) GMT+5:30
EMEA working hours (GMT): Showing the graph for EMEA working hours (8:00am GMT to 6:00pm GMT) GMT +00:00

If your data is in est better convert it to GMT first.

regards

Pradosh

Re: How to add constraint to Cyclic Group?

pradosh_thakur — Fri, 06 Oct 2017 15:30:37 GMT

PFA. I have adjusted it to you to show only the required time. Please adjust the if condition to make it work with GMT or EST. IST IS GMT+05:30 AND not GMT+09:30 . Adjust it and and you are good to go. hopefully it will help you write the condition.

regards

Pradosh

Re: How to add constraint to Cyclic Group?

Anonymous — Fri, 06 Oct 2017 18:23:17 GMT

How can I account for Daylight Savings dynamically?

Re: How to add constraint to Cyclic Group?

Anonymous — Fri, 06 Oct 2017 18:23:38 GMT

How can I account for Daylight Savings dynamically?

Re: How to add constraint to Cyclic Group?

pradosh_thakur — Fri, 06 Oct 2017 18:34:35 GMT

You can use a inline table with region name starting_workhour, end_workhour and fill them as required and use these field in the if clause.

I thought the qvw will solve the problem anyway Hopefully it will solve your problem If satisfied close the thread or else feel free to ask.

Regards

Pradosh

Re: How to add constraint to Cyclic Group?

pradosh_thakur — Sat, 07 Oct 2017 06:36:45 GMT

Did the qvw work for you? Did you try the inline table idea I pitched. Let me know if you need any help.

Regards

Pradosh

Re: How to add constraint to Cyclic Group?

Anonymous — Mon, 09 Oct 2017 14:15:45 GMT

Hi Pradosh,

The qvw did work for me- thank you!!! As of now, I'm using that solution. I may need to try out the inline table in the future, but for now the current qvw works.

thank you again for your help!

Best,

Karly