Current Year Previous Year Counts on the same report

[former_member648413]

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.

[former_member648413]

Hi Mohammad,

I have tried creating a brand new report this way. I added all the objects and filtered one data provider to Calendar Year = 2017 and the other to Calendar Year =2016. I have merged all the objects except Calendar Year. If I merge Calendar year and use merged object. It is not showing the data correctly. However, I am able to display current year and previous year on 2 separate tables.

Right now; I could see as below which shows correct values.

Is there a way I could do on the same table? Also, I want to see the day alignment like - If first day of the month in CY is FRIDAY ; it should show first FRIDAY of the month in the PY in the same row.

I tried - Relativedate(query2 CalendarDay;2) - It is showing first few days correctly. However, if there is a leap year ; it is breaking...and it is missing few days in between and the counts are not showing correctly. Only Days are going up. Not sure What is going on...

Let me know if there is a solution for that?

Thank you

[former_member648413]

When I merged it , it automatically created that object with a bracket.

See how it showing up attached.

[mhmohammed]

HI Amritha,

I don't think the formula =RelativeDate([DS0].[Calendar Day];-364) uses the merged object. As long as we see something before [Calendar Day], in this case we see [DS0]., or if it was [Query 1]. or [Query 2]. that means you're not using the merged object.

The formula should be =RelativeDate([Calendar Day];-364). We can look at other issues one after the other.

Thanks,
Mahboob Mohammed

[former_member648413]

Hi Mahboob, I tried that as well. See below. It is showing 2015 data as well when there is no data in the DB.

-1 will subtract 365 days. Would it do 364 for year period formula?

I used = RelativeDate([DS0].[Calendar Day];-364) which is the merged object.

I used filter for 2017 In the first query for January. In the Previous Year table , Why is it showing all the dates instead it should show only the previous year dates which matches the first table?

When i used this = RelativeDate([Query 1].[Calendar Day];-364)

It is not showing the counts for PY in the table.Problem is I have to use Query1 for first table and I have to use Query2 for the second table. If I merge either of them, it is not populating correctly.

Any suggestions?

Thank you

[mhmohammed]

Hi Amritha,

For PY_RO_CloseDate, why don't you use merged object [Calendar Day] instead of [Query 1].[Calendar Day]?

Use the formula as =RelativeDate([Query 1].[Calendar Day];-364) (ideally you should use =RelativeDate([Query 1].[Calendar Day];-1;YearPeriod))

Thanks,
Mahboob Mohammed

[former_member648413]

This is my requirement which is in Excelsheet.

[former_member648413]

i Have data for 2016 Jan-Dec and 2017- Jan only for now to test. So we have only Jan month to test for current year , previous year.

I used Calendar Day from Previous year query. I can see the data .. however, I am not seeing the day alignment now.

when I did current date -365, I could see the day alignment. Requirement is to see day alignment on the same row for each month.

merged all the columns except the measure column . See the screenshots.

Also, I am using the PY Date from CY as I wanted DayName Alignment.

Here I used the Current year column - 365 to get the previous year so that I see day alignment. When I Used Query 1 in Query 2 fields. I dont see data. Is there any other way to populate these while showing day alignments.

[mhmohammed]

Hi Amritha,

What columns do you have in the table, where you aren't able to see PY counts? Can you please add a picture, we'd like to see the exact values coming in that table.

Thanks,
Mahboob Mohammed