topic Re: YTD-Values in Pivot-Table in App Development

YTD-Values in Pivot-Table

BorisM — Thu, 26 Jan 2023 12:03:04 GMT

Hello, everyone,

I'm new here.
I have a question about how the data is shown in a pivot table.
I need to show certain measures under date dimension. When I select a day (Dimension "Datum"), the metrics in the table must be presented for the last 7 days (
see example).
I did that with the following formula:

Sum({<[Datum] = {">$(=Date(Max([Datum])- 7)) <=$(=Max([Datum]))"}>} [PCA_COUNT])

But I also have to show the following measures figures in the same table (in the example file are measures marked in yellow)
1. YTD for the selected day - 8 days (if the YTD measure is in the previous year, then no values)
2. YTD for the Selected Day
3. YTD for the selected day but previous year

Is that possible?
It would be very grateful, if you could answer the questions.

Best Regards
Boris

Re: YTD-Values in Pivot-Table

Steven35 — Thu, 26 Jan 2023 14:10:57 GMT

Hi Boris,

I think it is possible but each measure (Measure 1, Measure 2, Measure3) would have to be in dimension, and the values in column would have to be the measures.
Let me explain. First create a Measure table in the script.

For instance:

Measures: LOAD * Inline [ Measure 1 2 3 ];

Put"Measure" in the row dimension of the pivot table.

Then, for each of the 10 columns, you have to put in the Analysis set(here it is for the first column for instance):

Pick( Measure, // Measure 1 Sum({<Datum={">=$(=YearStart([Datum]))<=$(=Date(Max([Datum])- 7))"} >}Value), // Measure 2 Avg({<Datum={">=$(=YearStart([Datum]))<=$(=Date(Max([Datum])- 7))"} >}Value), // Measure 3 Max({<Datum={">=$(=YearStart([Datum]))<=$(=Date(Max([Datum])- 7))"} >}Value))

You will have to do this in the 10 column and change the filters in set analysis for each as you want.

I hope that's clear enough,

Steven

Re: YTD-Values in Pivot-Table

BorisM — Thu, 26 Jan 2023 15:08:01 GMT

Hello Steven,

thank you very much for your quick answer.
I think the solution is very cool.
The problem is that I have to build about 15 measures. In addition, I have to display the data for 14 days + YTDs (7 days was just an example).
There is a possibility that the solution is in the analysis layer. For example with dynamic dimensions...
P.S. The app is relatively large. The QVD file is around 6GB.
The app update needs to be fast so there can't be complex logic in scripts.

Best Regards
Boris

Re: YTD-Values in Pivot-Table

Steven35 — Thu, 26 Jan 2023 16:50:48 GMT

Boris,

I found another way by using a dimension that combines Date and YTD. However, this is more difficult to set up. In the script, you have to create a special calendar table that includes YTD and Date.
For example we could have:

Date_Key	Type_Calendar	Date_Selection	Special_Dim
21/04/2022	N	21/04/2022	21/04/2022
21/04/2022	YTD	21/04/2022	YTD
20/04/2022	YTD	21/04/2022	YTD
19/04/2022	YTD	21/04/2022	YTD
18/04/2022	YTD	21/04/2022	YTD
17/04/2022	YTD	21/04/2022	YTD
16/04/2022	YTD	21/04/2022	YTD

But for this method, you will have to create as many "Type Calendar" as there are different YTD's.
Here we link to the main table with Date_Key and in the app we select the date with Date_selection.
For exexample,
If you select 21/04/2022 in Date_selection and the type N it will show data for 21/04/2022
If you select 21/04/2022 in Date_Selection and the type YTD it will show data for 01/01/2022 to 21/04/2022
And it works I tried:

Hope you will understand,

Best regards

Re: YTD-Values in Pivot-Table

BorisM — Fri, 27 Jan 2023 11:33:57 GMT

Hello Steven,

understood.

Thank you for your enlightenment. If it doesn't work in the Analys layer, I try to reformulate the request so that the YTD values are not presented in a table with date fields.

Best regards

Boris.