calendar week in graphs

  • 2
  • Problem
  • Updated 2 years ago
  • Solved
Hello,

we are on UK locale - so week runs Monday to Sunday

Graph on Skuid page is grouping Friday to Thursday

Makes management reporting very hard

Help!

Ben
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
  • Help quick!

Posted 2 years ago

  • 2
Photo of Karl Li

Karl Li, Employee

  • 708 Points 500 badge 2x thumb
Hey Ben,

Could you post a screenshot? I think I might be able to help you, but I want to understand what kind of graph you're using first...

Thanks,
Karl
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
Karl,

here you can see the column chart - this is based on a count of end dates for actions in the table below. 
Week 42 (second column) has 9 actions,
In UK local (Monday to Sunday) week 42 is 17th to 23rd Oct, however as the second screen shot shows the 9 actions have end dates that range from 14th Oct to 20th Oct - that is to say over weeks 41 (10th to 16th) and week 42 (17th to 23rd)

Ben.
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
Karl,

Do you have any more thoughts?

This is a material issue for us - so any help would be great

Cheers,

Ben
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
Karl,

Do you have any more thoughts?

This is a material issue for us - so any help would be great

Cheers,

Ben
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
Karl,

Still waiting for help on this. Is there someone else who can pick this up?

Cheers,

Ben.
Hello Ben

What jumps to my mind, when i see this is the chart might be set up with wrong Axis specifications.



On the default date granulatiry of the chart you can select Calendar Week which should group you the Records Monday to Sunday rather than Friday to Thursday
(Keep in mind the Default Date Granularity field is only available if the Category field is a Datefiled, not a Datetime field!)
Photo of Matthias

Matthias

  • 628 Points 500 badge 2x thumb
Hello Ben

What jumps to my mind, when i see that problem is, the chart may have wrong configurations in the category Axis. I attached a screenshot of the category axis which is working in a CET timezone (Had no UK-Testsetting right away).

The Default Date Granularity and Additional Granularities only show up if the Category Field is a Date field (NO DATETIME!).

If you still have problems with it, you can paste me the XML of the Chart you are using and i can take a deeper look into it.  
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
Matthais,


Thank you for picking this up. The field is a Date field. Here is the XML

<skuidvis__chart model="Actions4graph" maintitle="Actions" type="column" uniqueid="sk-1mldN4-702" stacking="true" height="200px">                                            <dataaxes>
                                                <axis id="axis1" title="Actions"/>
                                            </dataaxes>
                                            <categoryaxes>
                                                <axis id="categories" categorytype="field" field="amc__End_Date__c" fillvals="true" dategranularity="calweek">
                                                    <allowedgranularities>
                                                        <type>day</type>
                                                        <type>calmonth</type>
                                                    </allowedgranularities>
                                                </axis>
                                            </categoryaxes>
                                            <serieslist>
                                                <series valuefield="amc__Action_Name__c" splittype="field" modelId="Actions4graph" categoryField="amc__End_Date__c" aggfunction="count" splitfield="amc__Action_Owner__r.Name">
                                                    <actions>
                                                        <action type="showPopup">
                                                            <conditions>
                                                                <condition type="contextrows" field="Id" mergefield="Id" operator="in" autocreated="true"/>
                                                            </conditions>
                                                            <popup title="New Popup" width="90%">
                                                                <components>
                                                                    <skootable showconditions="true" showsavecancel="true" showerrorsinline="true" searchmethod="server" searchbox="false" showexportbuttons="false" pagesize="10" createrecords="false" model="Actions4graph" buttonposition="" mode="read" uniqueid="sk-1n53mw-1073" emptysearchbehavior="query">
                                                                        <fields>
                                                                            <field id="Name" valuehalign="" type="" allowordering="true"/>
                                                                            <field id="amc__Action_Name__c" valuehalign="" type="" allowordering="true"/>
                                                                            <field id="amc__Action_Owner__c" valuehalign="" type="" allowordering="true"/>
                                                                            <field id="amc__Start_Date__c" valuehalign="" type="" allowordering="true"/>
                                                                            <field id="amc__End_Date__c" valuehalign="" type="" allowordering="true"/>
                                                                            <field id="amc__Status__c" valuehalign="" type="" allowordering="true"/>
                                                                            <field id="amc__Dependant_Action__c" valuehalign="" type="" allowordering="true"/>
                                                                            <field id="amc__Milestone__c" valuehalign="" type="" allowordering="true"/>
                                                                            <field id="amc__Milestone__r.amc__Project__c" valuehalign="" type="" allowordering="true"/>
                                                                            <field id="amc__Milestone__r.Service_line__c" valuehalign="" type="" allowordering="true"/>
                                                                        </fields>
                                                                        <rowactions>
                                                                            <action type="edit"/>
                                                                            <action type="delete"/>
                                                                        </rowactions>
                                                                        <massactions usefirstitemasdefault="true">
                                                                            <action type="massupdate"/>
                                                                            <action type="massdelete"/>
                                                                        </massactions>
                                                                        <views>
                                                                            <view type="standard"/>
                                                                        </views>
                                                                        <conditions>
                                                                            <condition type="contextrows" field="Id" mergefield="Id" operator="in"/>
                                                                        </conditions>
                                                                        <searchfields/>
                                                                        <filters>
                                                                            <filter type="multiselect" filteroffoptionlabel="New Filter" createfilteroffoption="true" affectcookies="true" autocompthreshold="25" conditionsource="auto" labelmode="auto" conditionfield="amc__Status__c" conditionoperator="in"/>
                                                                            <filter type="multiselect" filteroffoptionlabel="New Filter" createfilteroffoption="true" affectcookies="true" autocompthreshold="25" conditionsource="auto" labelmode="auto" conditionfield="amc__Action_Owner__c" conditionoperator="in"/>
                                                                        </filters>
                                                                    </skootable>
                                                                </components>
                                                            </popup>
                                                        </action>
                                                    </actions>
                                                </series>
                                            </serieslist>
                                            <colors>
                                                <value>#81d4fa</value>
                                                <value>#4dd0e1</value>
                                                <value>#26a69a</value>
                                                <value>#03a9f4</value>
                                                <value>#00acc1</value>
                                                <value>#00796b</value>
                                                <value>#0277bd</value>
                                                <value>#80deea</value>
                                                <value>#4db6ac</value>
                                                <value>#29b6f6</value>
                                                <value>#00bcd4</value>
                                                <value>#00897b</value>
                                                <value>#0288d1</value>
                                                <value>#00838f</value>
                                            </colors>
                                            <legend layout="off" halign="center" valign="bottom" showlabels="false"/>
                                            <renderconditions logictype="and"/>
                                        </skuidvis__chart>

Here is the category field:



Cheers,

Ben
Photo of Matthias

Matthias

  • 628 Points 500 badge 2x thumb
Hey Ben

I found out why this happens, the Week you are looking at is NOT what you would generally expect. It's based on ISO 8601 for time measurement. So for this year week 1 actually starts at a thursday.

So i think the best way to tackle this problem would be a UI-Only field which gives a "offset" to the weeknumber. I'll reply again when i found a fitting formula.
(Edited)
Photo of Matthias

Matthias

  • 628 Points 500 badge 2x thumb
Hey Ben

I finally found a solution which may help you get out of that misery:
When you create a UI-only Field with the following syntax

FLOOR((IF(MONTH({{amc__End_Date__c}})>=1, 31,0)+IF(MONTH({{amc__End_Date__c}})>=2, 28,0)+IF(MONTH({{amc__End_Date__c}})>=3, 31,0)+IF(MONTH({{amc__End_Date__c}})>=4, 30,0)+IF(MONTH({{amc__End_Date__c}})>=5, 31,0)+IF(MONTH({{amc__End_Date__c}})>=6, 30,0)+IF(MONTH({{amc__End_Date__c}})>=7, 31,0)+IF(MONTH({{amc__End_Date__c}})>=8, 31,0)+IF(MONTH({{amc__End_Date__c}})>=9, 30,0)+IF(MONTH({{amc__End_Date__c}})>=10, 31,0)+IF(MONTH({{amc__End_Date__c}})>=11, 30,0)+IF(MONTH({{amc__End_Date__c}})==12, 31,0)+DAY({{amc__End_Date__c}})+IF((CEIL(YEAR({{amc__End_Date__c}})/400)==YEAR({{amc__End_Date__c}})/400),1,IF((CEIL(YEAR({{amc__End_Date__c}})/100)==YEAR({{amc__End_Date__c}})/100),0,IF((CEIL(YEAR({{amc__End_Date__c}})/4)==YEAR({{amc__End_Date__c}})/4),1,0))))/7)


And base your graph off this UI-only field rather than the actual date field, it should fix your problem

What it does is, it calculates the "number of week" in the way you and i are used to
(Edited)
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
Matthais,

thank you for the solution - I really apprceaite your taking the time to solve it!

Is that a Skuid thing or a Salesforce thing? The ISO thing doesn't seem particularly useful for any of us that live in the real world!

Ben
Photo of Matthias

Matthias

  • 628 Points 500 badge 2x thumb
It is a Technical standard, it makes sense to keep it since certain devices will get confused with different standards (Not necessarily in SF context but when regarding Week of year in general), but from a human perspective it doesn't seem intuitive.

So this is most likely while SF has adopted this standard. But at this point i can only guess about sense and nonsense. if you want more information why this is built that way, i'd recommend asking the salesforce support.