Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Dates in Set Analysis

Several aspects of the Qlik search mechanism has been described in previous posts. There is however one that has not been covered: Search in dual fields, e.g. dates. This post will try to explain the basics.

When making searches in text fields, you can search either by using a normal search or by using a wildcard search, and when you search in numeric fields you can use a numeric search. But what about dual fields, like dates, where you have both a textual and a numeric representation?

The answer is displayed in the picture below.

search h.png

Normal searches and wildcard searches are straightforward and need not be explained. Numeric searches are also possible and do pretty much what you expect them to.

You should however note that the search string in a numeric search must contain the correct formatted date. It is in most cases not possible to use the numeric value of the date. E.g. you cannot search for 42005 when you want Jan 1st 2015, even though this is the value of the date.

The same logic is used in Set Analysis, which means that a correct Set Analysis expression with a date could look like this:

Sum( {$<Date={"<=2015-02-28"}>} Amount)

Often you want the Set Analysis expression to be dynamic, and then you need to put a dollar expansion with an aggregation function inside it. One case is that you want to compare the selected month with the preceding month. In principal, the solution is something similar to the following:

Selected (Last) month: Sum( {$<Month={"$(=Max(Month))"}>}  Amount )

2nd Last month:           Sum( {$<Month={"$(=Max(Month)-1)"}>}  Amount )

The Max(Month) will calculate the last possible month, and the dollar expansion will enter this value into the expression before the expression is parsed.

How the expression looks after the dollar expansion can be seen in the column header of a QlikView table. The above formulas have been used in the table below. Note that the dollar expansions with Max(Month) have been replaced with numbers.

QV Table.png

So far, so good.

However, the above formulas will not work. First, if you have created the Month using the Month() function, the field is cyclic which means that December of one year has a higher numeric value than January the following year, although it comes before January. Hence, the Max() function will not respect the order of months belonging to different years.

Secondly, the Month field has a dual value. This means that the Max(Month) will return a numeric when you need the textual value (‘Dec’) in the Set analysis expression.

One solution is to use a sequential month instead, and format it the same way everywhere:

Script:

Date(MonthStart(Date),'MMM-YY') as Month,

Expressions:

Sum( {$<Month={"$(=Date(Max(Month),'MMM-YY'))"}>} Amount )

Sum( {$<Month={"$(=Date(AddMonths(Max(Month),-1),'MMM-YY'))"}>} Amount )

Here the field Month is a date - the first day of the month - but formatted with just month and year. In other words: A number that equals roughly 42000 and is formatted as ‘Jan-15’. The same formatting is applied inside the dollar expansion. Note the column headers below.

QV Table2.png

Often it is practical to put the calculation of the Set analysis condition in variables. This way, the formula is kept in one place only and the Set analysis expressions become simpler and easier to read:

Script:

Set vLastMonth=      "=Date(Max(Month),'MMM-YY')";

Set v2ndLastMonth= "=Date(AddMonths(Max(Month),-1),'MMM-YY')";

Date(MonthStart(Date),'MMM-YY') as Month,

Expressions:

Sum( {$<Month={"$(vLastMonth)"}>} Amount )

Sum( {$<Month={"$(v2ndLastMonth)"}>} Amount )

Note that the variable definitions start with equals signs. This way they will be recalculated at every click.

Summary: Format the dates used inside Set analysis expressions, and use variables to simplify the expressions.

HIC

Further reading related to this topic:

The Search String

Data Types in QlikView

Cyclic or Sequential?

A Primer on Set Analysis

24 Comments
mayankraoka
Valued Contributor

Nice one.....

0 Likes
109 Views
mrooney
New Contributor III

Hi Henric,

I undestand what you say about properly date formatting. Right.

But don't you think it's far better to create sequential 'yearmonth' values in calendars in order to use them inside set analysis? I think it's not only more clear but, i guess, more effcient too. Am I right?

Best regards.

MR

0 Likes
109 Views
Employee
Employee

If you with a "sequential yearmonth" mean an integer defined as e.g.

    12*Year + Month as yearmonth

I would say that it would work, but I don't see the advantage. You would not need the Date() function inside the Set Analysis, that's true, but apart from that I can't see any big difference.

You could just as well define your yearmonth as

     Num( MonthStart( Date ) ) as yearmonth

and get the same effect. This definition has the added advantage that all date functions work flawlessly:

      Year( yearmonth )

      Month( yearmonth )

      Date( yearmonth, 'YYYY-MM' )


From a performance perspective, there should be no difference.

HIC

0 Likes
109 Views
lars_plenge
New Contributor III

Hi Henrik

I hope you can help, My issue is also about Dates in set analysis.


sum(aggr(NODISTINCT sum ({<KEY_Date = {42431,42432,42431}>}ITEM_COUNT),Calender.Date))

Date

ITEM_Count

42431

10

42431

10

42432

15

The result is only 25 because the duplicated date is ignored.

How can I fix my expression so I get 35.


Best regards

Lars Plenge

0 Likes
109 Views
Employee
Employee
  • I don't see why you need Aggr(). Just use Sum(ITEM_COUNT)
  • If you still need to use Aggr(), you should not use NODISTINCT. Just remove that qualifier.
  • Which formats do the dates really have? Check in a listbox where you don't have any number formatting. You should use the same format in your Set Analysis.

If you after checking the above still have a problem, I suggest you post this question in a thread of its own.

See also on Pitfalls of the Aggr function

Good luck.

HIC


0 Likes
109 Views
MVP
MVP

Hi Henric,

I've recently suggested some topics for your series of blog posts.

May I suggest another one?

(Not) all animals are created equal

Consider this INLINE table LOAD (BTW: using QV12IR, but same results on QV11.20 SR11):

LOAD Date(Num) as Date,

  Date(Num,'MM/DD/YYYY') as Date2,

  MonthName(Num) as Month,

  Num(Num,'#.00') as NumDec,

  Num,

  100 as Value;

LOAD * INLINE [

Num

42469

];

(you probably already know what I want to show here)

If I then create a straight table chart with dimension Num and some expressions with set expressions according this blog post:

2016-04-09 17_12_20-Edit Expression.png

I do get the expected results:

Num Sum({<Date = {'09.04.2016'}>}Value) Sum({<Date2 = {"04/09/2016"}>}Value) Sum({<Month = {'Apr 2016'}>}Value) Sum({<NumDec = {"42469.00"}>}Value)
100 100 100 100
42469100100100100

What if I remove the explicite formatting in the field modifier:

imageFile.png

??

Num Sum({<Date = {'09.04.2016'}>}Value) Sum({<Date2 = {'04/09/2016'}>}Value) Sum({<Month = {'42461'}>}Value) Sum({<NumDec = {'42469.00'}>}Value)
100 100 0 100
424691001000100

And what if I do a numeric search?

Num Sum({<Date = {">0"}>}Value) Sum({<Date2 = {">0"}>}Value) Sum({<Month = {">0"}>}Value) Sum({<NumDec = {">0"}>}Value)
0 0 100 100
4246900100100

Hm...

Though all fields are created as duals, using Qlik Date&Time functions or Num(), the results are not consistent, at lease I can't see a reason why they should differ.

I thought about that maybe the tag (like $date) is somehow controlling the behaviour, but there seems to be no difference in field tags.

I can see that returning a dual from Max() function might be useful (though the Help says it's returning a numeric, and it's not returning a dual in every cases, as we see), though I think this behaviour was different in older QV versions, returning a numeric in every case.

To question something on top of the Max() behaviour, why is

=FirstSortedValue(Date, -Num)

returning 42469, where I would assume it should return the dual value here?

Seems like I haven't really understood the basics of dual value handling in QlikView in all these years

Hope you can shed some light on this, probably best in a different thread / blog post.

Best,

Stefan

109 Views
juleshartley
Valued Contributor

I agree with Stefan - I've come across confusing situations with Duals where the behaviour did not match my understanding based on what I have read, particularly with reference to set analysis.. clarification would be useful.

0 Likes
109 Views
isaaclin
New Contributor III

Valuable information, thank you!

0 Likes
109 Views
Not applicable

I'm trying to use set analysis to pull a date completely out of a line graph.

Sum({$<CurDay -={"$(=Date(Date#('5/30/2016','MM/DD/YYYY'),'MM/DD/YYYY'))"}>}[Causal Total Time])

This what I tried.. ^^ My dimension for date is "CurDay", my measurement is "Causal Total Time"... I am showing the amount of downtime over the period of days but do not want to include May 30, 2016 in my graph.

How can I pull that date from my data completely?

0 Likes
109 Views
Employee
Employee

If CurDay has been interpreted as a date (at load time) and is formatted according to 'MM/DD/YYYY', then your expression ought to work.

I would however define a flag in the script, e.g.

If(CurDay=MakeDate(2016,5,30),0,1) as IsIncluded,

and then use this in my set analysis expression:

Sum({$<IsIncluded ={1}>} [Causal Total Time] )


Its clearer, and you can debug what happens by looking at the flag.

HIC

0 Likes
109 Views
lauracastagna
Contributor

Hi Henric,

can you please help me understand the Dates here:-

I have set the following two variables, vCurrentMonth and vCurrentYear. I have checked each value in the Variable Overview:-

vCurrent_Month= Sep and vCurrent_Year= 2016

So I don't understand when using the variables in the following formulas only formula A works (using vMonth_Current).  The second formula using the vCurrent_Year picks up the same values as A.

A.  Count( {$<Date={vMonth_Current}>} EngagementType)

B. Count( {$<Date={vYear_Current}>} EngagementType)

cheers,

Laura

0 Likes
109 Views
Employee
Employee

You seem to be mixing different data types.

Date is (usually) a dual with a numeric value that equals roughly 42000.

Month is (usually) a dual with a numeric value that is between 1 and 12.

Year is a numeric value that equals roughly 2000.

So, you need to first make sure that you are matching dates with dates and years with years.

Secondly, the expression {$<Date={vMonth_Current}>} will try to match the date with the string 'vMonth_Current'. You should instead use a dollar expansion and quotes to use the value of the variable: {$<Date={'$(vMonth_Current)'}>} .

HIC

0 Likes
109 Views
lauracastagna
Contributor

thanks Henric.

I understand the second bit but not the first.

In my calendar the year loads correctly.  Year(TempDate) As Year  I.e. this displays as 20016, 2015 etc.

But the variable I have created displays as DDMMYYYY -

LET $(vYear_Current) = Year(Today());  displays as

Else is there a document or utube or other you can please direct me to so I can understand this?

0 Likes
109 Views
Employee
Employee

Assuming that you have

   LET vYear_Current = Year(Today());
   LET
vDate_Current = Date(Today());

then you can use

   {$<Year={‘$(vYear_Current)’}>}
   {$<Date={‘$(vDate_Current)’}>}

but you cannot use

   {$<Date={‘$(vYear_Current)’}>}

as you do in your initial example

HIC

0 Likes
109 Views
MVP
MVP

Laura, it seems to me that you are using a field vYear_Current in addition to your variable (because you are displaying that field in a list box.

Maybe the values of that field are indeed dates.

Besides that, you should double check your variable definition:

LET $(vYear_Current) = Year(Today());

The dollar sign expansion of vYear_Current variable is probably not what you want to do here.

P.S. It might be better if you could create a separate thread for your issue, where you can also attach e.g. your sample application or script.

0 Likes
109 Views
Not applicable

Hi Henric,

I am struggling with this table. I want to show the Current Month for the Current Year and for Prior Years show always December data. I have added the below calculated dimension using aggr and MaxMonth.

I am able to bring February but the same month is shown in other years.

How can I show the prior years with December data and current year with current month?

Dimension

Year =if(aggr((Sum({$<Year={'>=$(vSelectedYearM3) <= $(vSelectedYear)'},Quarter=,Month={'$(vMaxMonth)'}>}Amount)),Year,Month)>0,Year)

Month

Expression = (Sum({$<Year=,Quarter=,Month=>}Amount))/1000

Current Table:

Year Month Amount
2014Feb289474
2015Feb311880
2016Feb345527
2017Feb365164

Expected result:

YearMonthAmount
2014Dec286262
2015Dec342752
2016Dec363261
2017Feb365164

Very appreciated your help.

Thanks,

Marcelo

0 Likes
109 Views
pljsoftware
Contributor III

Hi Marcelo,

you can use a flag field. In script generate a field where all December are

marked as 1 and for the current year is 1 only for use last month.

In set analysis only in expression you can use that field to select what

you need.

Best regards

Luca Jonathan Panetta

Il giorno 12/mar/2017 11:09, "Marcelo Mtanous" <qcwebmaster@qlikview.com>

ha scritto:

Qlik Community <https://community.qlik.com/?et=blogs.comment.created>

Dates in Set Analysis

nuovo commento di Marcelo Mtanous

<https://community.qlik.com/people/marcmta81?et=blogs.comment.created> - Visualizza

tutti i commenti su questo post del blog

<https://community.qlik.com/blogs/qlikviewdesignblog/2015/09/28/dates-in-set-analysis?et=blogs.comment.created#comment-58589>

0 Likes
109 Views
Not applicable

It worked, many thanks Luca

0 Likes
109 Views
jacobluft
New Contributor III

Hello,

Can you explain why when I follow the steps above and use them in my data I get this result?

Capture.PNG

My script and variables follow the posts instructions and my expression within the New Column, which should show the increase or decrease in Seat Count from one Month to the next is:

Sum({$<Month={"$(vLastMonth)"}>}[Seat Count])-Sum({$<Month={"$(v2ndLastMonth)"}>}[Seat Count])

I understand that it is inserting the part relative to the minus sign into the month before instead of using it as part of an aggregation.

Additionally how do I make it so that it performs the above calculation for all month, not just for the Max (June in this case)?

0 Likes
109 Views
hector_munoz_in
Valued Contributor

Thanks for sharing, Henric! Very clear!

0 Likes
109 Views
bvssudhakar
Contributor

Hello hic,

I have table with multiple date columns (nearly 5 date columns are there), Now I want create one master date column for all of them because in my report i can't to show multiple list boxes for the user. If i show like that user will get confuse.

Example: i am creating one bar chart/table for title name wise placements for this i use title name as dimension and =Count({<plcdate = {"*"}>}CandidateID) as expression

and now i have to create one list box for plcdate column

And now, again i am creating one bar chart/table for title name wise Declines for this i use title name as dimension and =Count({<Declinedate = {"*"}>}CandidateID) as expression

for this i have to create one more list box for Decline date column


Again i am creating one bar chart/table for title name wise Offered Candidates for this i use title name as dimension and =Count({<Offerdate = {"*"}>}CandidateID) as expression

for this i have to create one more list box for offer date column


But user is not accepting these many list boxes separately. they want only two list boxes year and month or hierarchy calendar list box that will apply to all date columns

So, How to handle this issue i am not understanding. I have visited some threads also those are not useful for me

Please suggest some solutions and help me out from this

Thank you in advance

0 Likes
109 Views
Employee
Employee

Take a look at Canonical Date I think that this could be a way forward.

HIC

0 Likes
109 Views
pradeepsagwal
New Contributor II

Hello hic‌ and friends

I want to use the same formulae but within terms of a fiscal year.

I am using you script for fiscal year.

How to get FYearMonth ??

dual(fYear & '-' & FMonth,Date(MonthStart(Date,$(vFM))))  as fYearMonth,

dual(  FMonth & '-' & fYear,Date(MonthStart(Date,$(vFM))))  as fMonthYear,

Above are not working when I am using

=date(max(fYearMonth),'MMM-YY') in text object/

It  gives Dec-18 whereas by maximum date is 08/20/2018.

Thanks in advance

Sagwal

0 Likes
109 Views
beck110979
Valued Contributor III

very helpful, thank a lot for sharing

0 Likes
102 Views