Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
karlykrebs
New Contributor III

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
Honored Contributor II

Re: How to add constraint to Cyclic Group?

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
MVP & Luminary
MVP & Luminary

Re: How to add constraint to Cyclic Group?

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

karlykrebs
New Contributor III

Re: How to add constraint to Cyclic Group?

Hmm that doesn't seem to work for me.

MVP & Luminary
MVP & Luminary

Re: How to add constraint to Cyclic Group?

Please provide a small example.

- Marcus

Partner
Partner

Re: How to add constraint to Cyclic Group?

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
karlykrebs
New Contributor III

Re: How to add constraint to Cyclic Group?

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

MVP & Luminary
MVP & Luminary

Re: How to add constraint to Cyclic Group?

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

karlykrebs
New Contributor III

Re: How to add constraint to Cyclic Group?

That doesn't seem to change anything either.

MVP & Luminary
MVP & Luminary

Re: How to add constraint to Cyclic Group?

Please provide a small sample-qvw.

- Marcus

karlykrebs
New Contributor III

Re: How to add constraint to Cyclic Group?

Here you go: