THIS PROBLEM IS FROM ORACLE ANALYTICS CLOUD, SO THE SQL QUERIES ARE DIFFERENT.
I HAVE A COLUMN WITH DIFFERENT PAYROLL PAYMENT PERIODS “S” FOR WEEKS AND “Q” FOR FORTNIGHTS, ETC.
I NEED TO CREATE A LINE GRAPH LIKE THE ONE IN THE IMAGE.
BUT LET IT BE PER WEEK, SO I CREATE A NEW COLUMN WHERE THE “S” DEPENDING ON THEIR NUMBER ARE ASSIGNED A NEW NAME LIKE “SEM” AND THE “Q” ARE ASSIGNED THE SAME NAME OF “SEM” BUT DEPENDING ON WHAT WEEKS THEY WERE WITHIN, EXAMPLE “Q01” = “SEM01”, “Q01” = “SEM02”, “Q01” = “SEM03” AND SO ON.
THEN THE FOLLOWING CALCULATION WAS CREATED
CASE WHEN PERIODOF = 'SEM01' THEN COUNT(DISTINCT CASE WHEN PERIODO = 'S01' THEN ID_SAP END) + COUNT(DISTINCT CASE WHEN PERIODO = 'Q01' THEN ID_SAP END) WHEN PERIODOF = 'SEM02' THEN COUNT(DISTINCT CASE WHEN PERIODO = 'S02' THEN ID_SAP END) + COUNT(DISTINCT CASE WHEN PERIODO = 'Q01' THEN ID_SAP END) WHEN PERIODOF = 'SEM03' THEN COUNT(DISTINCT CASE WHEN PERIODO = 'S03' THEN ID_SAP END) + COUNT(DISTINCT CASE WHEN PERIODO = 'Q01' THEN ID_SAP END) WHEN PERIODOF = 'SEM04' THEN COUNT(DISTINCT CASE WHEN PERIODO = 'S04' THEN ID_SAP END) + COUNT(DISTINCT CASE WHEN PERIODO = 'Q02' THEN ID_SAP END) WHEN PERIODOF = 'SEM05' THEN COUNT(DISTINCT CASE WHEN PERIODO = 'S05' THEN ID_SAP END) + COUNT(DISTINCT CASE WHEN PERIODO = 'Q02' THEN ID_SAP END) WHEN PERIODOF = 'SEM06' THEN COUNT(DISTINCT CASE WHEN PERIODO = 'S06' THEN ID_SAP END) + COUNT(DISTINCT CASE WHEN PERIODO = 'Q03' THEN ID_SAP END) WHEN PERIODOF = 'SEM07' THEN COUNT(DISTINCT CASE WHEN PERIODO = 'S07' THEN ID_SAP END) + COUNT(DISTINCT CASE WHEN PERIODO = 'Q03' THEN ID_SAP END)
THE HIGHEST POINTS ARE WHERE THE SUM OF WEEK AND FORTWEEK EMPLOYEES IS FOUND AND THOSE LOWER ARE ONLY COUNTING THE WEEKS
THE PROBLEM IS THAT THE SUM OF THE DIFFERENT THINGS IS ONLY GIVEN IN THE FIRST “SEM” THAT IS FOUND WITHIN “Q” SO MY GRAPH IS INCORRECT.
COULD YOU HAVE AN IDEA HOW I COULD CORRECT IT?
THANKS
THE DIFFERENT ONES OF EACH PERIOD WILL BE ADDED AND IT WILL BE GRAPHED EVENLY, BUT IT HAS MANY VARIATIONS
SAUCEDO D is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1