Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Set analysis - exclude current year AND month

Hello,

I would like to exclude the current year AND month from my chart, but it doesn't work :

sum(

    {

        <field_month -= {"$(= month(ReloadTime()) )"}> * <field_year -= {"$(= year(ReloadTime()) )"}>

    }  

nb_seconds)

/ 60

Please help, best regards

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Set analysis - exclude current year AND month

Hi everyone, I found how to resolve my problem,

First, I have added a new "special_period_field" to my LOAD which have this format : 'YYYYMM' (extracted from a the date field)

Then I have created a new variable :

vSpecialPeriod = year(ReloadTime()) & num( month(ReloadTime()), '00') // Returns YYYYMM value

=> The reason why I have created this variable is that QlikView don't know how to interpret this expression in the set analysis condition.

Finally I've used this condition in my set analysis :

sum( {< foo_field = {"foo_value"}, special_period_field -= {"$(=vSpecialPeriod)"} >}  numericField )

To conclude I would say that QlikView looks like a great tool but Set Analysis SUCKS !

View solution in original post

11 Replies
Highlighted
Honored Contributor II

Re: Set analysis - exclude current year AND month

Hi,

If you are excluding the current year then what is the point to exclude the current month. it is automatically excluded.

also you can also use today() function instead reloadtime().

HTH

Sushil

Highlighted
Partner
Partner

Re: Set analysis - exclude current year AND month

You could have month year column then, use that as expression in set analysis to filter.

eg :  sum({<field_MY={'vCurrentMonthYear'}>} value)

or

if you have calender, then flag current month dates to some binary value so you can use that in if condition ..

sum(if(currentMonthFlag=0,value) where currentMonthFlag=1 for current month dates else 0

Highlighted
Not applicable

Re: Set analysis - exclude current year AND month

Hi,

I want to exclude the month and the year only if both of them matches the reloadtime's month and year. If I only exclude the last year, i will lose all the months of the excluded year, that's not my goal.

The datetime must corresponds to the last reload datetime, not the server's datetime, this is why I use reloadtime() instead of today() function.

Highlighted
Partner
Partner

Re: Set analysis - exclude current year AND month

Hi Patil,

I didn't get ur question.Can you explain it clearly what you want ?

Reagrds,

Nagarjuna

Valued Contributor

Re: Set analysis - exclude current year AND month

Hi,

You want to exclude only Current Day or max Reloadtime Month ??

Neha S.

Highlighted
Not applicable

Re: Set analysis - exclude current year AND month

I think that your first solution should work, but I think it's dirty because I have to create a new field that I will only use for this filter.

I would like to create a set analysis with the good conditions.

Highlighted
Not applicable

Re: Set analysis - exclude current year AND month

My current datetime is ReloadTime(), I want to keep all the months and years of my calendar except the month and year which correspond to ReloadTime() 's month and year.

Highlighted
Not applicable

Re: Set analysis - exclude current year AND month

I want to create a Set Analysis which contain a filter on my month and year fields, because I don't want to retrieve the last period obtained thanks to ReloadTime function.

I want to retrieve all the months and years, except the month and year which correspond to ReloadTime() 's month and year.

Highlighted
Valued Contributor

Re: Set analysis - exclude current year AND month

try

sum({<field_date =- {<MonthName(today())>}>}field)