Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to add constraint to Cyclic Group?

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:

 

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

  

How can I enable these constraints?

1 Solution

Accepted Solutions
pradosh_thakur
Master II
Master II

Hi

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

  1. US working hour (EST) : Showing the graph for  US working hours (7:00am EST to 5:00pm EST)   GMT-04:30
  2. India working hour (IST): Showing the graph for India working hours (9:00am IST to 6:30pm IST)    GMT+5:30
  3. 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

Learning never stops.

View solution in original post

18 Replies
marcus_sommer

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

Anonymous
Not applicable
Author

Hmm that doesn't seem to work for me.

marcus_sommer

Please provide a small example.

- Marcus

SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey
Anonymous
Not applicable
Author

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

marcus_sommer

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

Anonymous
Not applicable
Author

That doesn't seem to change anything either.

marcus_sommer

Please provide a small sample-qvw.

- Marcus

Anonymous
Not applicable
Author

Here you go: