I hope someone is able to help me with this problem that has been bugging me for a number of months.
I have a claim file and a premium file that is extracted using SQL. I have tried to join the two files, but each time I join them, it does an inner join and compresses them. I tried to create a date file which contains the month and year.
For the premium and claim file, I created an ID which is made of Type and Province. This ID joins the two files without compressing them. I joined the date in premium to the date in the date file and use this as my base for the dates.
I can create the report to a point. If I add the claim amount to the dimension field, it duplicates the months for where there was a claim. If I add the claim to the expression field, not all the values show.
Please see the attached excel sheet for what I have and what I am trying to achieve.
Thanks in advance.