Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

eiconsulting
Contributor

SET Analysis - date filter

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



1 Solution

Accepted Solutions
Highlighted

SET Analysis - date filter


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

9 Replies
Not applicable

SET Analysis - date filter

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
Contributor

SET Analysis - date filter

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)



Not applicable

SET Analysis - date filter

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.

Not applicable

SET Analysis - date filter

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.

Not applicable

SET Analysis - date filter

I use this :

right

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

so, its easier to manage dates.





Highlighted

SET Analysis - date filter


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
Contributor

SET Analysis - date filter

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
Contributor III

SET Analysis - date filter

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.. Tongue Tied please help

shumailh
Contributor III

SET Analysis - date filter

I got the answer in other discussion thanks guys


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


Community Browser