topic SET Analysis - date filter in QlikView

SET Analysis - date filter

eiconsulting — Tue, 09 Jun 2009 20:37:13 GMT

I have a formula like this (where MonthOb is a variable out of a slider)

Sum

(if(month_chius=MonthOb,qta_ven))

Following my "learning curve" and lots of advice on topic, I thought to replace it with

Sum

({$<month_chius={$(#MonthOb)}>}qta_ven)

It doesn't work because the $ replacement puts a NUMBER within brackets as a selection. Though the number content works with the if clause apparently it doesn't as a set parameter. If I replace the field with the literal "Feb" instead of "2" it does work.

Sum({$<month_chius={Feb}>}qta_ven)

I also am trying some formulas to obtain the month without success but don't see why it should get so complicated if the field content is a number. Where is the mistake?

Flavio

SET Analysis - date filter

Tue, 09 Jun 2009 21:47:56 GMT

Could you post a sample? This one may be a little easier if we could see what format your data is in.

From your description, it seems like you have stored the month as a string (e.g. 'Jan'). Is this the case? Do you have it anywhere where the month is stored as a number? I find it much easier to use numerical months as then you're not left wondering whether to use Jan, January, etc. If you don't have a numeric month (or a normal date) within your data, it may be worthwhile to add it in. Here's a recent discussion on going from month number to name, but the same ideas would apply: http://community.qlik.com/forums/t/17054.aspx

You could try using something like: ({$<month_chius={$(#=Date(MakeDate(1900, MonthOb), 'MMM'))}>}qta_ven)

SET Analysis - date filter

eiconsulting — Tue, 09 Jun 2009 23:11:14 GMT

I tried other times to post files without success. I would have anyway to make a simpler one as a sample.

Already looked at the post you linked. The *** thing is that the variable contains a number. I displayed it with a text box to make sure.

Obviously there is the usual SET in the script to provide IT month names:

SET

MonthNames='Gen;Feb;Mar;Apr;Mag;Giu;Lug;Ago;Set;Ott;Nov;Dic'; DateFormat='DD/MM/YYYY';

I tried with your formula adapted (replace text mese=month) if you place the expression in a text box the MMM (Feb) comes out. The same expr within the {} results in a 153 (days after 1900 - if you change the year the value increases) value that I detect using a blank label where the value is evaluated prior to using it in the table. Allow me to say that this would be a workaround. There is no reason why the if clause accepts a value and the SET should need the linked description. It is like in within {} the FORMAT is sterylized.

=Date(MakeDate(1900, MeseOb,1), 'MMM')

Sum

({$<mese_chius={$(#=Date(MakeDate(1900, MeseOb,1), 'MMM'))}>}qta_ven)

SET Analysis - date filter

Tue, 09 Jun 2009 23:37:34 GMT

Yes, I think you're stuck with the variable being a number. My suggestion was to use your Load script to load "mese_chius" in as a number too. Using the INLINE suggestion in that other post, you could link "mese_chius" to an INLINE table that would add a month number to your dataset.

INLINE [

mese_chius, mese_chius_num

Feb, 2 ....

That way you would have the month name and the month number as separate fields and could use whichever one suited the situation.

In order to attach a file, click the Options tab while composing your message and use the Add/Attach button. All you would need is a really simple sample. Just something that would use your field names and show what format your data is in. I don't have any sample data that stores a month name, so I don't have anything to test on. When doing Set Analysis, I usually end up doing guess and check a few times until I hit the correct formula.

SET Analysis - date filter

Tue, 09 Jun 2009 23:49:53 GMT

Here, I whipped up a quick sample. My formula doesn't seem to work with the named month. It seems like it should.

I added an INLINE load to link your month name (mese_chius) to a month num (mese_chius_num). Then I compared the month num to the MeseObj variable (which is also a number) and I think I used the original set analysis formula you set up. It works fine.

SET Analysis - date filter

Tue, 09 Jun 2009 23:59:22 GMT

I use this :

right

(year(RefDate_Mov),2) & num(month(RefDate_Mov),00) as PeriodoNum

so, its easier to manage dates.

SET Analysis - date filter

rwunderlich — Wed, 10 Jun 2009 09:53:24 GMT


Flavio wrote:
It doesn't work because the $ replacement puts a NUMBER within brackets as a selection

The # in the variable expression forces a number. Have you tried it without the #?

-Rob

SET Analysis - date filter

eiconsulting — Thu, 11 Jun 2009 14:48:47 GMT

THANKS, Rob, YOU ARE RIGHT!!!! I copied acritically a sample with the # ... without it the "turn-around solution" goes right and the formula WORKS and I can proceed. Sum ({$<mese_chius={$(=Date(MakeDate(1999, MeseOb,1), 'MMM'))}>}qta_ven)

Just for the sake of pure KNOWLEDGE and to answer to other advice, we are speaking of a local variable attached to a slider. My initial question was why would the IF clause work and the same field would not produce the same result within a SET instruction (that needs the "date conversion" function that was not working for the # sign)?

The (mese_chius = MeseOb) is evaluated differently from <mese_chius={$(#MeseOb)}> With or without the #sign before MeseOb a number is substituted and it expects MMM text (?).

Managing the month number month text (MM MMM) with an INLINE structure is surely one way to avoid implicit conversion.

Flavio

SET Analysis - date filter

shumailh — Mon, 25 Oct 2010 15:16:59 GMT

Dear Rob,

I have similar kind of issue. I am trying to get the report date which is the last month end date i.e. '09/30/2010'. I put this value in the variable throught the same table where all the Month end date present. please see the below code.


Script:
CardMast_Active:
LOAD Report_NO, LoadDate_Active, LoadDay_Active, bal, AIF FROM [$(Outputpath)CardMast_Active.qvd] (qvd);

MinMaxDates:
load
 minstring(LoadDate_Active) as StartDate,
maxstring(LoadDate_Active) as ReportDate
resident CardMast_Active

// now get the values...
 let ReportDate = peek('ReportDate',0) ;

Expression:
=COUNT({$<AIF = {'P'}, LoadDate_Active = {$(=Date($(#=ReportDate),'MM/DD/YYYY'))} >} AIF)

The above expression is not working.. please help

SET Analysis - date filter

shumailh — Mon, 25 Oct 2010 15:45:17 GMT

I got the answer in other discussion thanks guys


LoadDate_Active = {"$(=Date($(#=ReportDate),'MM/DD/YYYY'))"} >} AIF)