Hi Henk,
yeah, I agree with you it's a complex req but you did it, Thanks!!
the provided code is working fine for me appreciate it, Henk.
------------------------------
P S
------------------------------
Original Message:
Sent: Wed May 10, 2023 03:26 PM
From: HENK CAZEMIER
Subject: Quarterly Data
It can be done, but it's complex. I've attached the report spec that defines the above output.
E.g. the expression for qty-PPPQ is:
case when [ORDER_DATE]
between
_add_days(_add_months([cLastDayOfQ], -12),1)
and _add_months([cLastDayOfQ], -9)
then [Quantity] end
------------------------------
Kind regards,
Henk Cazemier
------------------------------
Original Message:
Sent: Wed May 10, 2023 12:17 PM
From: P S
Subject: Quarterly Data
Hi Henk,
Thanks for the reply, it's a good start.
I wrote some code similar to this but the Quarter data are stored in data items, right? And yes the above code which you have given is working but need those quarters as columns to get count for the report.
basically need like this:
Dimension Q2-2022 Q3-2022 Q4-2022 Q1-2023
row1
row2
Thanks!!
------------------------------
P S
Original Message:
Sent: Wed May 10, 2023 09:41 AM
From: HENK CAZEMIER
Subject: Quarterly Data
Based on the gosales database, modeled in a Data module.
The sorting of the date-related information will be easier when the format would be yyyy-Qn, which is what the following expression gives:
cast(_year([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE]),varchar(4))
+ '-Q'
+ cast(floor((_month([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE]) + 2)/3) ,varchar(1))
The filter for the last 12 months could be:
[C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE] <= ?last_order_date?
and _add_years([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE],1) > ?last_order_date?
Hopefully this will give you a starting point.
------------------------------
Kind regards,
Henk Cazemier