Set Analysis with Month as Dimension in a Dimension Table

[AndreSchwarze]

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!

[TauseefKhan]

@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])

[AndreSchwarze]

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.

[Kushal_Chawda]

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

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

[AndreSchwarze]

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

[Kushal_Chawda]

@AndreSchwarze  there was a typo. try below

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

[AndreSchwarze]

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...

[Kushal_Chawda]

@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])

[AndreSchwarze]

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.

[Kushal_Chawda]

@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.