topic Re: Set Analysis and mapping functions in QlikView

Set Analysis and mapping functions

Checco — Fri, 16 Sep 2022 08:34:40 GMT

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

Re: Set Analysis and mapping functions

marcus_sommer — Fri, 16 Sep 2022 13:21:26 GMT

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

- Marcus

Re: Set Analysis and mapping functions

Checco — Sat, 17 Sep 2022 10:37:46 GMT

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

Re: Set Analysis and mapping functions

marcus_sommer — Sun, 18 Sep 2022 12:01:36 GMT

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

Re: Set Analysis and mapping functions

Checco — Wed, 21 Sep 2022 16:42:20 GMT

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

Re: Set Analysis and mapping functions

marcus_sommer — Thu, 22 Sep 2022 06:08:21 GMT

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