PQL Date Query
I currently have a list that is relying on a parameter in a date format of "Mmm YYYY". This controls two separate formulations that utilise the YTD & lag functions so I can get a YTD and then a prev years YTD premium. This works as expected.
However, I also want to add a formulation that uses the Parameter of say Apr 2022 and then gives me a full year total premium for the previous year based on whatever date range is selected in the parameter ("Mmm YYYY"). I have tried to incorporate 'add years' into the formula but when changing from Apr 2022 to Mar 2022, the previous years premium total changes - so something I'm doing isn't right!
A few screen shots attached.
Thanks,
Dan
5 replies
-
Hi Dan,
Some clarification please.
When you select the parameter, as Apr 2022, do you want the formulation to return the total premium changes for the whole of 2020, Jan through Dec, or 2021, Jan through Apr 2021?
Thanks,
Ian
-
Hi Ian,
Thanks for getting back to me.
When selecting Apr 2022 I wanted it to show the premium for the entire previous year. So as we progress through 2022, the premium will be showing a total premium for 2021. Currently the Apr 2022 drives a YTD figure which gives you the YTD at any given point in time but I wanted to also use this to drive the entire previous years premium total.
If we changed the month from Apr 2022 to Apr 2021, then the premium showing will be the total premium for 2020. It will be in a separate column in the grid just so you can compare YTD figures vs the entire previous years premium at any given point in time.
I think we may have got it working using the parallel periods function as per attached and then creating an aggregate of this via the right clicking on the measure but am now running into trouble with sorting the elements in a way I want.
Thanks,
Dan
-
Thanks Dan,
Can you please tell me the data source type, SQL or MDX?
Does the Month / Year item make up Date hierarchy?
Thanks,
Ian
-
Hi Dan,
Here you go. I'm assuming you have a dimension of something like [Full Month Name] which contains members such as [Feb 2022] and one of [Year] which contains members such as [2022].
Let's build it up on stages:
1. You have your selected month year as a parameter:
[Data].[Date full month name].@[030ea2aa-f168-4c19-bf7a-e94f53b9ff05]
2. You need to find the Year in which this month exists, so CrossJoin the selected month year with the years. this will return a list (of one element) of years that contain this month year:
NonEmpty(AllMembers([Data].[Date year]),CrossJoin([Data].[Date full month name].@[030ea2aa-f168-4c19-bf7a-e94f53b9ff05]))
3. We Need to pick off the first member (the year that contains the full month year selected):
FirstElement(NonEmpty(AllMembers([Data].[Date year]),CrossJoin([Data].[Date full month name].@[030ea2aa-f168-4c19-bf7a-e94f53b9ff05])))
4. Get the Member prior to the picked off member
FirstElement(NonEmpty(AllMembers([Data].[Date year]),CrossJoin([Data].[Date full month name].@[030ea2aa-f168-4c19-bf7a-e94f53b9ff05]))).Lag(1)
5. Get the value for that year from the measure concerned:
(FirstElement(NonEmpty(AllMembers([Data].[Date year]),CrossJoin([Data].[Date full month name].@[030ea2aa-f168-4c19-bf7a-e94f53b9ff05]))).Lag(1),[Measures].[Sales])
6. Use Item in report:
Life would be made easier if you had a regular hierarchy, Year --> Month --> Date or something similar, then all you would need to do is get the parent of the month year and go back one step.
Hope that helps.
Ian
-
sorry Ian was off on Friday, many thanks for the above, will go through it and see what I get. Looks to be exactly what I'm after though.
Many thanks, as ever!
Dan