Problem with straight table for multiple date columns

[bvssudhakar]

Hi Exprets,

Here I am attaching my qvw. in this file i am having multiple date columns for that i have created master calendar for them and also created charts also. But user gave one more requirement.

User wants one Straight table with following fields.

1. Department

2. TitleName

3. jobid

4. No.of postions: (Expression is =Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},DateType={'plcdate'}>}CandidateID) )

5. Enter Date

6. Placement Date.

7. Count of Days between Enter Date and placement Date

I am getting the problem (getting '-' only for date fields and count of days fields) to create the straight table with those fields.

Can you guys please check the qvw and help me out to achieve this

[vishsaggi]

Hi Sudhakar,

You have lot of synthetic keys in your datamodel. You have to fix them first. I have updated the script and attaching your qvw back. Just reload the file and see the data model and also your straight table if it is populating correct dates.

[bvssudhakar]

Hi Vishwarath Nagaraju,

I am trying to reload the script these errors i am getting, Can you please check once and let me know why those errors coming

[pooja_prabhu_n]

Hi,

This is the issue with your data model, dates are not linked properly.

Whenever Enter date is present , Placement date and plc dates are null and vice versa.

So it is not possible to Count of Days between Enter Date and placement Date, because any one of the date is null all the time.

Use canonical dates to link all the dates and then create master calendar

Canonical Date

Please remove synthetic keys in the data model.

Thanks,

Pooja

[bvssudhakar]

Hi Pooja Nagaraj,

I have tried with Canonical Date also,

Canonical Date Linking Problem

See that thread and please give some solution because i tried so many solutions but i am not getting proper solution

Thank you in advance

[bvssudhakar]

If you want i will share qvd also. please try for solution as per my requirement

Thank you in advance

[pooja_prabhu_n]

Hi,

I have modified the script .

PFA qvw file.

[bvssudhakar]

Hi,

I tried with this script also then that time my all values are changing in charts and text object. Previously all are correct that time i am not able to create only straight table with that requirement. But Now i am getting all wrong values

Can you please check for that also

Thank you in advance

[Frank_Hartmann]

have a look into pooja´s qvw.

if you supress zeros for placementdate in dimension tab of the straight table in sheet3 you will get the 72 when selecting Year=2018.

in textobject you will need this expression to get the 72:

=Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},Date_Flag={'plcdate'},placementdate={'<>null()'}>}CandidateID)

[pooja_prabhu_n · Accepted solution]

Hi,

I have modified the script. Looks like now the data is matching.

JobEndDate starts from 1/1/1900 12:00:00 AM , hence master calendar has generated year from 1900.

Please find the updated qvw as attached.

Thanks,

Pooja