How to add constraint to Cyclic Group?

[Anonymous]

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?

[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]

Hmm that doesn't seem to work for me.

[marcus_sommer]

Please provide a small example.

- Marcus

[SergeyMak]

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

[Anonymous]

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]

That doesn't seem to change anything either.

[marcus_sommer]

Please provide a small sample-qvw.

- Marcus

[Anonymous]

Here you go: