Trouble calculating denominator

[cbaqir]

I am trying to get the Single Denom field to show 12 is Agenda Year is NOT 2019 and 8 if it is 2019 for the count of months to calculate the average. It works some of the time but not all of the time. Any idea why? TIA!

=if(year(today())=year(if(GOV_DECISION_BODY_SUB=PRIMARY_DECISION_BODY,date#(yearstart(PRIMARY_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=PRIMARY_DECISION_COMMITTEE_CAT,date#(yearstart(PRIMARY_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=SECONDARY_DECISION_BODY,date#(yearstart(SECONDARY_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=SECONDARY_DECISION_COMMITTEE_CAT,date#(yearstart(SECONDARY_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=THIRD_DECISION_BODY,date#(yearstart(THIRD_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=THIRD_DECISION_COMMITTEE_CAT,date#(yearstart(THIRD_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=FOURTH_DECISION_BODY,date#(yearstart(FOURTH_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=FOURTH_DECISION_COMMITTEE_CAT,date#(yearstart(FOURTH_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=FIFTH_DECISION_BODY,date#(yearstart(FIFTH_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=FIFTH_DECISION_COMMITTEE_CAT,date#(yearstart(FIFTH_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=SIXTH_DECISION_BODY,date#(yearstart(SIXTH_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=SIXTH_DECISION_COMMITTEE_CAT,date#(yearstart(SIXTH_DECISION_AGENDA_DATE))))))))))))))),num(Month(today())),12)

[rubenmarin]

Hi, it fails because it has many diffrent values for PRIMARY_DECISION_BODY, PRIMARY_DECISION_AGENDA_DATE... so it returns null.

Maybe you can do all that "if's" in script to create an agenda date assigned to each row, from that date you can create a calendar to work with years, months...

It will help with performance and to keep expressions a lot clearer, maybe just:

If(Year(Today())=AgendaYear, Num(Month(Today())), 12)

[cbaqir]

I am not sure how to accomplish that. Even if I did something like this, I'm not sure how to maintain the star schema to a calendar table: 

LOAD DEFECT_ID,
GOV_DECISION_BODY_SUB,
PRIMARY_DECISION_BODY,
PRIMARY_DECISION_COMMITTEE_CAT,
PRIMARY_DECISION_AGENDA_DATE,
month(PRIMARY_DECISION_AGENDA_DATE) as PRIMARY_DECISION_AGENDA_DATE_MONTH,
year(PRIMARY_DECISION_AGENDA_DATE) as PRIMARY_DECISION_AGENDA_DATE_YEAR,
SECONDARY_DECISION_BODY,
SECONDARY_DECISION_COMMITTEE_CAT,
SECONDARY_DECISION_AGENDA_DATE,
month(SECONDARY_DECISION_AGENDA_DATE) as SECONDARY_DECISION_AGENDA_DATE_MONTH,
year(SECONDARY_DECISION_AGENDA_DATE) as SECONDARY_DECISION_AGENDA_DATE_YEAR,
THIRD_DECISION_BODY,
THIRD_DECISION_COMMITTEE_CAT,
THIRD_DECISION_AGENDA_DATE,
month(THIRD_DECISION_AGENDA_DATE) as THIRD_DECISION_AGENDA_DATE_MONTH,
year(THIRD_DECISION_AGENDA_DATE) as THIRD_DECISION_AGENDA_DATE_YEAR,
FOURTH_DECISION_BODY,
FOURTH_DECISION_COMMITTEE_CAT,
FOURTH_DECISION_AGENDA_DATE,
month(FOURTH_DECISION_AGENDA_DATE) as FOURTH_DECISION_AGENDA_DATE_MONTH,
year(FOURTH_DECISION_AGENDA_DATE) as FOURTH_DECISION_AGENDA_DATE_YEAR,
FIFTH_DECISION_BODY,
FIFTH_DECISION_COMMITTEE_CAT,
FIFTH_DECISION_AGENDA_DATE,
month(FIFTH_DECISION_AGENDA_DATE) as FIFTH_DECISION_AGENDA_DATE_MONTH,
year(FIFTH_DECISION_AGENDA_DATE) as FIFTH_DECISION_AGENDA_DATE_YEAR,
SIXTH_DECISION_BODY,
SIXTH_DECISION_COMMITTEE_CAT,
SIXTH_DECISION_AGENDA_DATE,
month(SIXTH_DECISION_AGENDA_DATE) as SIXTH_DECISION_AGENDA_DATE_MONTH,
year(SIXTH_DECISION_AGENDA_DATE) as SIXTH_DECISION_AGENDA_DATE_YEAR
FROM
TB17_20190816_141053.xlsx
(ooxml, embedded labels);

[rubenmarin]

Hi, I think that the whole 'if' you use as calculated dimension should be in the script:

=if(year(if(GOV_DECISION_BODY_SUB=PRIMARY_DECISION_BODY,date#(yearstart(PRIMARY_DECISION_AGENDA_DATE)),
if(GOV_DECISION_BODY_SUB=PRIMARY_DECISION_COMMITTEE_CAT,date#(yearstart(PRIMARY_DECISION_AGENDA_DATE)), ...

... as AgendaDate,

So you end with the date you need for each row, and this AgendaDate field is the one linked to master calendar.

[sunny_talwar]

New Script (just like mentioned by @rubenmarin)

LOAD DEFECT_ID, 
     GOV_DECISION_BODY_SUB, 
     PRIMARY_DECISION_BODY, 
     PRIMARY_DECISION_COMMITTEE_CAT, 
     PRIMARY_DECISION_AGENDA_DATE, 
     SECONDARY_DECISION_BODY, 
     SECONDARY_DECISION_COMMITTEE_CAT, 
     SECONDARY_DECISION_AGENDA_DATE, 
     THIRD_DECISION_BODY, 
     THIRD_DECISION_COMMITTEE_CAT, 
     THIRD_DECISION_AGENDA_DATE, 
     FOURTH_DECISION_BODY, 
     FOURTH_DECISION_COMMITTEE_CAT, 
     FOURTH_DECISION_AGENDA_DATE, 
     FIFTH_DECISION_BODY, 
     FIFTH_DECISION_COMMITTEE_CAT, 
     FIFTH_DECISION_AGENDA_DATE, 
     SIXTH_DECISION_BODY, 
     SIXTH_DECISION_COMMITTEE_CAT, 
     SIXTH_DECISION_AGENDA_DATE,
	 if(year(if(GOV_DECISION_BODY_SUB=PRIMARY_DECISION_BODY,date#(yearstart(PRIMARY_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=PRIMARY_DECISION_COMMITTEE_CAT,date#(yearstart(PRIMARY_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=SECONDARY_DECISION_BODY,date#(yearstart(SECONDARY_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=SECONDARY_DECISION_COMMITTEE_CAT,date#(yearstart(SECONDARY_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=THIRD_DECISION_BODY,date#(yearstart(THIRD_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=THIRD_DECISION_COMMITTEE_CAT,date#(yearstart(THIRD_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=FOURTH_DECISION_BODY,date#(yearstart(FOURTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=FOURTH_DECISION_COMMITTEE_CAT,date#(yearstart(FOURTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=FIFTH_DECISION_BODY,date#(yearstart(FIFTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=FIFTH_DECISION_COMMITTEE_CAT,date#(yearstart(FIFTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=SIXTH_DECISION_BODY,date#(yearstart(SIXTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=SIXTH_DECISION_COMMITTEE_CAT,date#(yearstart(SIXTH_DECISION_AGENDA_DATE)))))))))))))))>=2017,
	 year(if(GOV_DECISION_BODY_SUB=PRIMARY_DECISION_BODY,date#(yearstart(PRIMARY_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=PRIMARY_DECISION_COMMITTEE_CAT,date#(yearstart(PRIMARY_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=SECONDARY_DECISION_BODY,date#(yearstart(SECONDARY_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=SECONDARY_DECISION_COMMITTEE_CAT,date#(yearstart(SECONDARY_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=THIRD_DECISION_BODY,date#(yearstart(THIRD_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=THIRD_DECISION_COMMITTEE_CAT,date#(yearstart(THIRD_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=FOURTH_DECISION_BODY,date#(yearstart(FOURTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=FOURTH_DECISION_COMMITTEE_CAT,date#(yearstart(FOURTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=FIFTH_DECISION_BODY,date#(yearstart(FIFTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=FIFTH_DECISION_COMMITTEE_CAT,date#(yearstart(FIFTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=SIXTH_DECISION_BODY,date#(yearstart(SIXTH_DECISION_AGENDA_DATE)),
	 if(GOV_DECISION_BODY_SUB=SIXTH_DECISION_COMMITTEE_CAT,date#(yearstart(SIXTH_DECISION_AGENDA_DATE)))))))))))))))) as [Agenda Year]
FROM
TB17_20190816_141053.xlsx
(ooxml, embedded labels);

LOAD * INLINE [
    Year, CountMonths
    2019, 8
    2018, 12
    2017, 12
    2016, 12
];

And once you do that, the denominator can be just this

=If(Year(Today()) = [Agenda Year], Num(Month(Today())), 12)