topic Re: Set Analysis with Month as Dimension in a Dimension Table in App Development

Set Analysis with Month as Dimension in a Dimension Table

AndreSchwarze — Fri, 06 Sep 2024 10:54:24 GMT

Hello Qlik-Community,

I´m working on the following issue.
I have a table with certain business units and want to create two KPI in a table next to each other:
1. Turnover per Business Unit YTD (no problem)
2. Turnover per Business Unit previos Year until Month(today())

It should look like this:

Business Unit	Turnover YTD	Turnover prev. YTD (until Month(today()))
A	1000	800
B	5000	6000
C	3000	3500
D	200	400

For the first bullet point my syntax looks as follows (I´m including and excluding certain parameters)
SUM(
{< V_BelegKopf.BelegArt={"C","I"},
[MASTER_CAL.Year]={'$(=Year(Today()))'},
[Client.Clientnumber] -={'0815'},
[Parts.Partgroup]-={'4711',1234','7896'},
[Client.Country]={'DE','AT', 'CH', 'LU'} >}
[Document.Total])

It creates the desired output per each busines unit in the table.

For the second bullet point/third row I tried the following:

SUM({<
{< V_BelegKopf.BelegArt={"C","I"}, ,
[MASTER_CAL.Year]={'$(=Year(Today())-1)'},
[MASTER_CAL.Month]={"<=$(=Month(Today()))"},
[Client.Clientnumber] -={'0815'},
[Parts.Partgroup]-={'4711',1234','7896'},
[Client.Country]={'DE','AT', 'CH', 'LU'} >}
[Document.Total])

It results in NULL-Values.
I tried also different versions including a range from Yearstart, changed " to ', did Month(addyears(today(),-1)) as parameter and a lot more. Nothing worked.

Addtionally I have a Graph to compare absolute and relative the differnces between both values with the additional dimension of the month (To display the difference by month) and the basic IF-Condition:
IF(MONTH(TODAY())>=[MASTER_CAL.Month] worked and only shows the months until today.

Copying that before the SUM()-Set is not working as well.
Thereof I´m looking for your support. Thank you in advance!

Re: Set Analysis with Month as Dimension in a Dimension Table

TauseefKhan — Fri, 06 Sep 2024 10:44:27 GMT

@AndreSchwarze

Ensure there are no extra commas or misplaced brackets in your set analysis.

SUM(
{< V_BelegKopf.BelegArt={"C","I"},
[MASTER_CAL.Year]={'$(=Year(Today())-1)'},
[MASTER_CAL.Month]={"<=$(=Month(Today()))"},
[Client.Clientnumber] -={'0815'},
[Parts.Partgroup]-={'4711','1234','7896'},
[Client.Country]={'DE','AT','CH','LU'} >}
[Document.Total])

Re: Set Analysis with Month as Dimension in a Dimension Table

AndreSchwarze — Fri, 06 Sep 2024 10:55:45 GMT

I corrected the original question. It was a mistake as I was typing instead of copy&pasting from my original code. Thank you for pointing that out.

Re: Set Analysis with Month as Dimension in a Dimension Table

Kushal_Chawda — Fri, 06 Sep 2024 11:45:14 GMT

@AndreSchwarze If you have date in your calendar, try below

Date ={">=$(=yearstart(addyears(today(),-1)))<=$(addyears(today(),-1))"}

Re: Set Analysis with Month as Dimension in a Dimension Table

AndreSchwarze — Mon, 09 Sep 2024 05:59:17 GMT

How is that related to month as a dimension?
Nevertheless I tried it - allocated it to MASTER_CAL.Date - and it did not work, but gives me a zero-Value

Re: Set Analysis with Month as Dimension in a Dimension Table

Kushal_Chawda — Mon, 09 Sep 2024 08:22:00 GMT

@AndreSchwarze there was a typo. try below

Date ={">=$(=yearstart(addyears(today(),-1)))<=$(=addyears(today(),-1))"}

Re: Set Analysis with Month as Dimension in a Dimension Table

AndreSchwarze — Mon, 09 Sep 2024 08:26:29 GMT

Unfortunatly the same result appears: Zero-Values.
But thank you for your efforts. I appreciate it.

I really don´t understand how to solve the issue as YTD is working perfectly fine...

Re: Set Analysis with Month as Dimension in a Dimension Table

Kushal_Chawda — Mon, 09 Sep 2024 08:50:30 GMT

@AndreSchwarze It should work unless you have selections on Month & Year. If you have selections on any other Calendar dimension, you need to exclude that in your set analysis

SUM({<
{< V_BelegKopf.BelegArt={"C","I"},
Date ={">=$(=yearstart(addyears(today(),-1)))<=$(=addyears(today(),-1))"},

Year=, Month =,
[Client.Clientnumber] -={'0815'},
[Parts.Partgroup]-={'4711',1234','7896'},
[Client.Country]={'DE','AT', 'CH', 'LU'} >}
[Document.Total])

Re: Set Analysis with Month as Dimension in a Dimension Table

AndreSchwarze — Mon, 09 Sep 2024 09:22:19 GMT

I have already excluded - better paraphrased - exchanged year and month with your suggestion. Nevertheless thank you for pointing to that explicitly:

SUM({<
{< V_BelegKopf.BelegArt={"C","I"}, ,
[MASTER_CAL.Date] ={">=$(=yearstart(addyears(today(),-1)))<=$(=addyears(today(),-1))"},
[Client.Clientnumber] -={'0815'},
[Parts.Partgroup]-={'4711',1234','7896'},
[Client.Country]={'DE','AT', 'CH', 'LU'} >}
[Document.Total])

The result was as above mentioned: Zero-Values.

Re: Set Analysis with Month as Dimension in a Dimension Table

Kushal_Chawda — Mon, 09 Sep 2024 09:32:42 GMT

@AndreSchwarze Without looking at your sample data or app it will be hard to say where the issue is. But expression I provided should work. Last thing I would like to highlight there is a typo (additional comma) in your expression, just see if resolving that works.

Re: Set Analysis with Month as Dimension in a Dimension Table

madelonjansen — Mon, 09 Sep 2024 09:42:19 GMT

What are the values in you Month field? Are these month names or numbers? You made sure the output of your set-analysis is the same as the values in the field?
(in case of a dual, set-analysis will always use the textual representation)