Set Analysis and mapping functions

[Checco]

Hello everybody
I ask if anyone can help me.
I have a table like:
LOAD * INLINE [
Id,   DateTest1,    Sales,   NObserv
1,    09/30/2022,   125,   1
1,    09/24/2022,   141,   2
1,    09/21/2022,   105,   3
1,    09/13/2022,   115,   4
2,    09/30/2022,   130,   1
2,    09/24/2022,   121,   2
2,    09/21/2022,     95,    3
2,    09/13/2022,   102,   4
];
and I wish I could create a table using set analysis as below:

That is, each column contains the sum of the 'Sales' field for each observation progressive:
column 1 -> NObserv = 1 (Date = '30 / 09/2022 ')
column 2 -> NObserv = 2 (Date = '24 / 09/2022 ')
column 3 -> NObserv = 3 (Date = '21 / 09/2022 ')

My problem is that I can't get the value of the corresponding date to appear in the label of each column.
I was thinking of using mapping.
Can anyone tell me where to find (link) documentation on the mapping function and, if possible, also some good examples?

Thank you all

[marcus_sommer]

Just use date as horizontally dimension and sum(Sales) as expression and then selecting the dates which you want to see.

- Marcus

[Checco]

Thanks Marcus for your reply.

I probably didn't understand.
The expression is fine as I did (with Set Analysis). This allows me to calculate for example the differences between contiguous columns.
Each column is calculated with expressions such as:
Sum ({$ <NObserv = {1}>} Sales)
Sum ({$ <NObserv = {2}>} Sales)
Sum ({$ <NObserv = {3}>} Sales)
...............................
My problem is how to make sure that in the label of each column appears the value of the date corresponding to the value of the variable NObserv:
1 <--> Label: 09/30/2022
2 <--> Label: 09/24/2022
3 <--> Label: 09/21/2022

.......

I hope I am clear

Thank Francesco 

[marcus_sommer · Accepted solution]

You may fetch the corresponding dates with something like:

only({$ <NObserv = {1}>} DateTest1)

or if there are multiple dates possible even with something like:

date(min({$ <NObserv = {1}>} DateTest1)) ' - ' & date(max({$ <NObserv = {1}>} DateTest1))

But like already hinted above - without a more specific reason you might not mandatory need multiple expressions else a single one and the appropriate dimensions will also return the views.

- Marcus

[Checco]

Thanks Marcus for your valuable suggestions.
I tried to make the changes that you indicated to me (if I understand correctly). See the attached file see the "Sales" chart. But there are a couple of problems:
1- Still in the "Sales" chart, how can I make the difference between two columns?
2-If I add other variables I can't correctly size the column width-. Because?

Also I'm trying to do something similar using variables (see "Comparison dates" chart).

Thanks 1000

Francesco 

[marcus_sommer]

Your origin query didn't mention that the aim is a comparing of certain selected values against each other and calculating a rate from it - therefore my suggestion to sum the values simply against the dimension.

Probably there are further possibilities to create comparison-views, for example with interrecord-functions like above/before or maybe alternate states but I think none would be more suitable as your chosen approach with multiple expressions - each picking one value respectively calculating their differences. You may consider to replace the variable-control against a selection-control by querying the min/max-values of Prog within the set analysis. Means instead of:

Sum({<Prog={$(v_Sel)}>} Sales)

you may use:

Sum({<Prog={"$(=min(Prog))"}>} Sales)

- Marcus