topic Trouble calculating denominator in QlikView

Trouble calculating denominator

cbaqir — Tue, 20 Aug 2019 21:10:45 GMT

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)

Re: Trouble calculating denominator

rubenmarin — Wed, 21 Aug 2019 10:52:10 GMT

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)

Re: Trouble calculating denominator

cbaqir — Wed, 21 Aug 2019 14:38:30 GMT

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

Re: Trouble calculating denominator

rubenmarin — Mon, 26 Aug 2019 18:44:55 GMT

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.

Re: Trouble calculating denominator

sunny_talwar — Mon, 26 Aug 2019 19:08:44 GMT

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)