Counting by the Day of the Week in a Pivot Table

Hello:

I am attempting to count incidents by the day of the week across a month. I am hoping for:

MON TUE WED THU FRI SAT SUN

Shift A 3 6 2 5 11 8 7

Shift B 2 8 x x x x x

Shift C and so forth.

Here is what I have so far, but I can't figure out how to tell SiSense to count only by one of the 7 days of the week and not duplicate the day of the week as pictured here:

Any help is greatly appreciated!

Darin Inda

dinda156

Posted 2 years ago·Last reply 2 years ago

5 comments

DRay

·2 years ago

Hello dinda156 ,

I wanted to follow up to see if the solution offered by Benji_PaldiTeam and TriAnthony worked for you.

If so, please click the 'Accept as Solution' button so other users with the same questions can find the answer faster. If not, please let us know so that we can continue to help.

Thank you.

Benjamin Nissim

·2 years ago

Hi dinda156 ,

If I understand the question correctly, one best approach would be to create a column in the Elasticube table that identifies the day of the week. You can then use this new column instead of 'Time in Alarm DateTime' in your screenshot.

To achieve this, use the following formula in Elasticube to get the day name:

DayName(DayOfWeek([<span class="hljs-keyword">Table</span> Name].[<span class="hljs-type">Date</span> Field]))

This will allow you to group and count incidents by the day of the week without duplication.

Feel free to reach out if you have further questions, we're always happy to help 🙂
inquiries@paldi.solutions
Paldi Solutions, Number #1 Sisense Plugins Developer

dinda156

OP2 years ago

I probably should have mentioned I am in a cloud environment. As such, no elasticube.

thanks!

dinda156

OP2 years ago

Hello:
Working in our cloud environment, SiSense (Cloud) doesn't like DAYNAME. I cannot reference a table in the cloud, only the data element. Our cloud implementation doesn't permit this.

Tri Anthony

·2 years ago · Edited

Hi dinda156,

Did you mean that the data model is a live model, or that you do not have access to the model? Do you have access to the Data tab (like shown below) at all?

The solution to your question requires changes to the data model (regardless of the model type, elasticube or live), so to achieve this, you'll need edit access to the model. Otherwise, you'll need to ask the data designer of the model to make the changes on your behalf.

Help and How-To

Counting by the Day of the Week in a Pivot Table

Hello:

I am attempting to count incidents by the day of the week across a month. I am hoping for:

MON TUE WED THU FRI SAT SUN

Shift A 3 6 2 5 11 8 7

Shift B 2 8 x x x x x

Shift C and so forth.

Here is what I have so far, but I can't figure out how to tell SiSense to count only by one of the 7 days of the week and not duplicate the day of the week as pictured here:

Any help is greatly appreciated!

Darin Inda

dinda156

Posted 2 years ago·Last reply 2 years ago

5 comments

DRay

·2 years ago

Hello dinda156 ,

I wanted to follow up to see if the solution offered by Benji_PaldiTeam and TriAnthony worked for you.

If so, please click the 'Accept as Solution' button so other users with the same questions can find the answer faster. If not, please let us know so that we can continue to help.

Thank you.

Benjamin Nissim

·2 years ago

Hi dinda156 ,

To achieve this, use the following formula in Elasticube to get the day name:

DayName(DayOfWeek([<span class="hljs-keyword">Table</span> Name].[<span class="hljs-type">Date</span> Field]))

This will allow you to group and count incidents by the day of the week without duplication.

Feel free to reach out if you have further questions, we're always happy to help 🙂
inquiries@paldi.solutions
Paldi Solutions, Number #1 Sisense Plugins Developer

dinda156

OP2 years ago

I probably should have mentioned I am in a cloud environment. As such, no elasticube.

thanks!

dinda156

OP2 years ago

Hello:
Working in our cloud environment, SiSense (Cloud) doesn't like DAYNAME. I cannot reference a table in the cloud, only the data element. Our cloud implementation doesn't permit this.

Tri Anthony

·2 years ago · Edited

Hi dinda156,

Did you mean that the data model is a live model, or that you do not have access to the model? Do you have access to the Data tab (like shown below) at all?