Date format and association

[Anonymous]

Hello  - Still learning about QV, but I am having trouble. The problem is with two tables that have a common date field called "Fiscal Period". This allows me to compare the sales forecast to statistical and orders data by the respective period.

The sales Orders and Sales FC originates from a QVD our IT team generates from SAP. The Stat forecast comes from an Excel table I generate from two different Excel files and then perform a conversion in a separate QV report (due to size) and then is converted to QVD (See script below which I abbreviated due to length). The two QVD's are then loaded in to my "Sales FC report".

I am having trouble with the interpretation of the date field in my Sales FC report as you can see in the table below as QV is interpreting the dates differently? Any help on what I can do to fix this as I believe I have tried just about everything I can think of?

[sunny_talwar]

The script above shows only one source of Fiscal Period... what is the other source?

[Anonymous]

Thank you for the help Sunny. Below is an abbreviated view of the script in my main QV report where I combine the two QVD's. Note the table in the first part of my question is from the QV report I use to build the Stat FC. Below is the script I use to combine the output of that report to the main FC report.

[sunny_talwar]

May be change the other Fiscal Periods to this

Date(MonthStart([Fiscal Period]), 'YYYY.MM') as [Fiscal Period]

[Anonymous]

Thanks again Sunny. Are you referring to changing this in my script with the cross table (ie. stat FC ) or in the QV report where I combine these (Sales FC + Stat FC)?

[sunny_talwar]

Ideally in the script, but if for some reason you are unable to do it in the script, then you can do it on the front end as a calculated dimension in the chart

[Anonymous]

Hello again Sunny, Thanks again. My report takes about 2.5 hours to refresh so I just got the results now after trying this change in the script to the other fiscal periods (Sales FC/Orders). Unfortunately, the change did not work. I am getting a little different result as you can see below where all the data coming from the Sales FC/Orders QVD is lumped in to one date as you can see below?

[sunny_talwar]

Would it be possible to share some raw data from the respective files to see what is going on?

[Anonymous]

I have attached a sample of the data from my Stat FC which originates from an Excel file and then is converted to QVD. It is my suspicion that the source of the problem is here. I am converting this data in a separate QV report which combines the two tables as seen in my script above. Then is stored as a QVD.

Note that before I combined this data with my Sales FC report, I had no issues with the Sales FC report. It was only when I added this new table that I started having the association issue, so is my thought this is what introduced the issue.