SET Analysis - date filter

[eiconsulting]

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

[#2]

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)

[eiconsulting]

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

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)

[#4]

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.

[#5]

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.

[#6]

I use this :

right

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

so, its easier to manage dates.

[rwunderlich · Accepted solution]

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

[eiconsulting]

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

[shumailh]

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

[shumailh]

I got the answer in other discussion thanks guys

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