Sum wrong then using GetFieldSelections(Period)-100 or (Max (total Period) -100)

[janus2021]

Hi.

I use the following function:
..
Period <= Max (total Period)
and
Period> (Max (total Period) -100)
..
(-100) to get 12 months but it does not seem to work in my calculation when I use sum. This is done in the same step as above:
Sum ({$ <[Anstm] = {'onGoing'}>} [Leave]) /
(Sum ({$ <[Anstm] = {'onGoing'}>} [nrbAnst]) / 12).

However, the summary will be correct if I use ... [nrbAnst]) / 10
Why??

Gets the same error in the summary when I use:
..
[Period] <= GetFieldSelections (Period)
and
[Period]> GetFieldSelections (Period) -100
..
Same here that the summary shows correctly when I use ... [nrbAnst]) / 10

Anyone can explain what is happening and what could be wrong?

[OmarBenSalem]

Wanna show the last 12 months?

Maybe sthing like 

sum({<Period={">$(=addmonths(monthstart(max(Period)),-12))">}>}YourMeasure)

That said, here's an old response of mine where I tried to explain step by step how to handle this kind of set expressions ! hope this helps you and others !

https://community.qlik.com/t5/New-to-Qlik-Sense/YTD-MTD-issue/td-p/1278297

[janus2021]

Hi.

but my measuere(calculation) looks like this: 

(Sum ({$ <[Anstm] = {'onGoing'}>} [Leave]) /
(Sum ({$ <[Anstm] = {'onGoing'}>} [nrbAnst]) / 12))

So i getting an error using: 

sum({<Period={">$(=addmonths(monthstart(max(Period)),-12))">}>}

              (Sum ({$ <[Anstm] = {'onGoing'}>} [Leave]) /
             (Sum ({$ <[Anstm] = {'onGoing'}>} [nrbAnst]) / 12))

      )

more ideas?

[OmarBenSalem]

Maube sthing like   

  (Sum ({$ <[Anstm] = {'onGoing'},Period={">$(=addmonths(monthstart(max(Period)),-12))">}>} [Leave]) /
             (Sum ({$ <[Anstm] = {'onGoing'},Period={">$(=addmonths(monthstart(max(Period)),-12))">}>} [nrbAnst]) / 12))

      you should really try to have a training on qlik set expressions ..!

[janus2021]

Yes i know i need training in Qlik, do you have any course recommendation in Set expressions? 

Then using the set expression i get:  Error in set modifier ad hoc element list: ',' or ')' expected.

[OmarBenSalem]

set expressions seems difficult at first sight, but then you'll love working with them.

Let's suppose you have a date field (you don't have to create variables); only a date field will do the job.

And let's suppose you've created a calendar in your script so you have Year,Month, Quarter and Week fields

YTD: if we select 12/04/2016: YTD will return our measure from 01/01/2016 to 12/04/2016

How we do that?

Suppose our measure is : sum(Sales)

1)First changes: sum({<date=,Year=,Month=,Quarter=>}Sales)

We add these to force Qlik to not take into consideration our selection of date for example.

Let me explain in better words, if you don't write the "date=" and select the date 12/04/2016; Qlik will filter all the data to that selected point and then return the sum(Sales) for the day 12/04/2016.

To prohibit this, we must write the date=.

2) Second change: sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)

Let explain this : date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}

We want to work from date=01/01/2016 to the selected date=12/04/2016 right?

So we're working with the field :

a) date={    }

b) Now we wanna this date to be <=selected date which is max(date) ;

max(date) is a function so it needs an "=" sign:

=max(date)

when we have a '=' we add the $ (before each calculation) : $(=max(date) ) => this is 12/04/2016

Now we add the <= so we'll have :  <=$(=max(date) )

for the second part, we want our date to be >=01/01/2016 which is the start of the year:

a) same approach, we use the YearStart function that returns the start of the selected year: >=$(=YearStart(Max(date)))

Now our expression is : from : date={    }

to : date={>=$(=YearStart(Max(date)))<=$(=Max(date))}

Now, in a set expression, if we wanna work with the year 2016 for example which is numeric: we call it without quotes:

Year={2016}

If we wanna focus on a country, Tunisia for example which is a string: we call it with quotes

Country={'Tunisia'}

In our case, we focusing on a range of dates created by an expression, so we surround it by double quotes:

date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}

Final expression for YTD:

sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)

Same approach for MTD:

sum({<date=,Year=,Month=,Quarter=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}Sales)

Hope this helps,

Omar,