Island data table help using within chart.

[Matt404]

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 ) )" 

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,

[Arthur_Fong]

Can you provide a sample app?

[Matt404]

Sample attached.

Thanks,

[Arthur_Fong]

Try this:

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

[Matt404]

{$(=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 · Accepted solution]

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]

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]

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]

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