topic Re: Set analysis - exclude current year AND month in QlikView

Set analysis - exclude current year AND month

Tue, 14 Apr 2015 10:06:22 GMT

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

Re: Set analysis - exclude current year AND month

sushil353 — Tue, 14 Apr 2015 10:09:36 GMT

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

Re: Set analysis - exclude current year AND month

manojkulkarni — Tue, 14 Apr 2015 10:32:11 GMT

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

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

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

Re: Set analysis - exclude current year AND month

Tue, 14 Apr 2015 12:19:28 GMT

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.

Re: Set analysis - exclude current year AND month

nagarjuna_kotha — Tue, 14 Apr 2015 12:22:40 GMT

Hi Patil,

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

Reagrds,

Nagarjuna

Re: Set analysis - exclude current year AND month

neha_shirsath — Tue, 14 Apr 2015 12:27:48 GMT

Hi,

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

Neha S.

Re: Set analysis - exclude current year AND month

Tue, 14 Apr 2015 13:05:13 GMT

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.

Re: Set analysis - exclude current year AND month

Tue, 14 Apr 2015 13:08:23 GMT

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.

Re: Set analysis - exclude current year AND month

Tue, 14 Apr 2015 14:33:35 GMT

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.

Re: Set analysis - exclude current year AND month

Anonymous — Tue, 14 Apr 2015 14:53:48 GMT

try

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

Re: Set analysis - exclude current year AND month

neha_shirsath — Wed, 15 Apr 2015 06:30:37 GMT

Hello,

Try this,

Sum({<DateField= e({"$(=Monthname(max(DateField)))"})>}Field)

Regards,

Neha S.

Re: Set analysis - exclude current year AND month

Wed, 29 Apr 2015 13:40:18 GMT

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 !