Current Year Previous Year Counts on the same report

129 Views

Hi All ,

I am struggling with this report.

1. I have Calendar Day, Calendar Year. A Measure Object, An indicator field.

2. I have 4 fields of measures. CPRO Count CY, QRO Count CY, CPRO Count PY, QRO Count PY

3. I have 2 Dates and 2 day names where i have to show in the report like:

CY Date, CY DayName, PY Date, PY DayName,CPRO Count CY, QRO Count CY, CPRO Count PY, QRO Count PY

5/1/2020,Friday,5/3/2019,Friday , and show counts for this specific day accordingly for CY and PY.

6. PYDate and DayName - I used RelativeDate(Date,-364) - It Is showing day name alignment correctly.

7. I have tried 2 data providers.

One is having (Calendar day -1) and the other with (same calendar day). When I am trying to merge this calendar day - I am not getting the results for PY Counts.

Current Year Date - ToDate(Merged Dimension [Calendar Day];"MM/dd/yyyy")

CY DayName- DayName(Merged Dimension [Calendar Day])

PY Date- RelativeDate(Merged Dimension [Calendar Day];-364)

PY DayName- DayName(ToDate([PY_Date];"M/d/yyyy"))

8. I got the counts correctly for CY. However, I am not getting counts for PY counts correctly. It is blank.

CY Counts - [Query 1].[Count] Where ([ Pay Indicator] = "Yes")

PY Counts- [Query 2].[Repair Order Count] Where ([Pay Indicator] = "Yes")

It is not picking previous year dates from PY Date.

Can someone help me in figuring this out WHY previous year counts are not showing on the same report? What Am i missing here?

Appreciate your help here!

Thank you

Amritha.