Qlik Sense: Show all Data until selected Month (Charts, KPI,..)

[surtex]

Hello Member,

I would like to show all Data until the month which I have selected.

For Example: I select Juli 2022 in my filter, so i would like to show the data from Jan 2022 to Juli 2022. 

On the one site I will show data in a bar char and on the other hand i have a KPI, where I would like to show YTD (Year to date).

Just now my expression is:

Sum({<Profit_Center_COPA = {'185', '186', '187', '188', '189', '152'}, Year = {'2022'}>}"EBIT adjusted")/1000 

I take a filter where always one month is selected. How can I incorporate this into the formula above?

The "EBIT adjusted" datas have a connection to Dimension calender with the field YearMonth: 202207 

Thanks a lot 🙂 

[martinpohl · Accepted solution]

Hi,

so change your expression to

Sum({<Profit_Center_COPA = {'185', '186', '187', '188', '189', '152'}, Year = {'2022'}, YearMonth={"<=$(=max(YearMonth))"}>}"EBIT adjusted")/1000

Regards

[BrunPierre]

Try this

Sum( {$<Profit_Center_COPA = {'185', '186', '187', '188', '189', '152'}, Year = {'2022'},YearMonth = {"<=$(#=max(YearMonth))"},Field1,Field2,Field3>} "EBIT adjusted")/1000

Where Field1, Field2 & Field3 are not to interfere with the YTD.

[surtex]

Thanks, both solutions are working 🙂 Can you write an expression where I must only select Month without year? Because the year is just filtert in the expression ...Year={'2022'}.. and I just want to show a Month filter. Yearmonths format as filter looks not nice.. 😞

[surtex]

I wrote the expression above with Month instead of YearMonth.. but it still not work.. 

[BrunPierre]

Try this set in your expression together with Year={'2022'} for the YTD.

[Date Field]={"<=$(=Max([Date Field]))"}

[surtex]

Hey Peter,

so I get only the value from the selected month, but not YTD.

My calender is structured:

The connection field between the facts table and calendar is Date2..

[surtex]

I found the problem myself.... but I don't care how to fix this... 

It's because the month field is not a number format. As soon as I try the formula with YearMonth or MonthNbr (1,2,3,4..) it works... and the solution with Max Date Field does not work.. 

[BrunPierre]

Likewise, Max Date doesn't work due to the format.

[surtex]

Now I create a new column in the calendar file like this: Month.Year (01.2022, 02.2022) and it does work. So the last question I have. In my expression I have year = '2022'.. but when I write Year={"Year(today())"} it does not work... do you have an idea? Thanks 🙂