Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Matt404
Contributor II
Contributor II

Island data table help using within chart.

Hello,
First time using Island data table. 
I have a master calendar with a 'Week' field (contains the week number)

My Summary table also has a 'Week' field (WeekLIVE) 

I created a Pivot table chart with the Dimension of 'Week' (from the master calendar) 

I want to show the sum of head per week

My expression is "sum( IF ( Week = WeekLIVE , WEEKLYHEAD ) )" 

Island table.png

as you can see from the data the sum for the week of the 27 should be 9029

63203 / 7 is 9029. but i don't want to have to add '/ 7' to my expression.

Because I'm working with weeks, and my calendar has days. do i need to do something different.???

If i select a single day in the week  i get the correct answer, but i shouldn't have to.

Thanks,

 

Labels (1)
1 Solution

Accepted Solutions
Matt404
Contributor II
Contributor II
Author

I Think i have worked it out. 

i used sum( IF( DateKey = WeeklyDate1, WEEKLYHEAD) ) 

as i have dates in both tables and the calendar should have all dates, i can still use the Week as the dimension.

So far this is working.

 

Thanks

View solution in original post

8 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Can you provide a sample app?

Matt404
Contributor II
Contributor II
Author

Sample attached.

Thanks,

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

sum({<WeekLIVE={$(=max(Week))}>} WEEKLYHEAD)

Matt404
Contributor II
Contributor II
Author

{$(=max(Week))} will evaluate to 52 (as 52 is the max value in the field Week) unless i select a week to look at. which then it will work, but i need to see all weeks in the same table 

 

 

Matt404
Contributor II
Contributor II
Author

I Think i have worked it out. 

i used sum( IF( DateKey = WeeklyDate1, WEEKLYHEAD) ) 

as i have dates in both tables and the calendar should have all dates, i can still use the Week as the dimension.

So far this is working.

 

Thanks

vishsaggi
Champion III
Champion III

Your calendar is not linked with your summary table. Please find attached qvw file with modified script. Let me know if this is what you are looking for. 

Matt404
Contributor II
Contributor II
Author

I didnt want them joined, i'm trying to get a better understanding of Island data tables. and then how to use them in a chart.

Thanks anyway. 🙂

 

Gabbar
Specialist
Specialist

This expression doesnt work when using any time filter as a chart.