Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
timmarsh
Contributor III
Contributor III

Trouble with dates and current selections in set expression

Hi all,

I read about 20 articles about previous periods before posting this, as this seems to be a really simple problem someone will know how to solve.


I can't post data, sorry, as I work in Health and our data is not de-identified and uses a data store.

So, I am trying to create a simple table showing the sum of something for this month, compared to last month.

My vMaxDate is 31/12/2017 at this stage.

Using a straight table, I can get all the data to display for Nov2017 using the below. (I realise there are more elegant ways of doing this with flags, something I'll get to eventually.) I realise I may have some redundant syntax - be gentle, I am still learning.

=sum({$<Date = {">=$(=monthstart(addmonths($(=vMaxDate),-1)))<=$(=monthend(addmonths($(=vMaxDate),-1)))"}, Day=>} DS_Complete_Counter)

I can also display the current month using:

=sum({$<Date = {">=$(=monthstart($(vMaxDate)))<=($(vMaxDate))"}>, Day=} DS_Complete_Counter)

However, if I select a date, let's say Jun 3rd, 2017, I want the table to do 2 things.

Previous month to show May and current month June.

However, when I choose that date, both cols in the table are blank, so it looks like the current selection isn't working?

Date is not a num, I have DateNum for that, and not stored as a dual.

Happy to share Cal or anything else (I am using the standard MasterCal).

Sorry to ask such a simple question.

2 Solutions

Accepted Solutions
sunny_talwar

Are you making selection in the same Date field as the one you have in your set analysis or is there another field that you make selection in?

=sum({$<Date = {">=$(=monthstart($(vMaxDate)))<=($(vMaxDate))"}>, Day=} DS_Complete_Counter)

View solution in original post

timmarsh
Contributor III
Contributor III
Author

This is the correct previous month expression.

I've no idea why the forcing of values of month, year and day to empty makes it correct.  I'd love to understand why!

=sum({$<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))<=$(=monthend(addmonths(Max(Date),-1)))"}, Day=, Year=, Month=>} DS_Complete_Counter)

View solution in original post

8 Replies
sunny_talwar

Are you making selection in the same Date field as the one you have in your set analysis or is there another field that you make selection in?

=sum({$<Date = {">=$(=monthstart($(vMaxDate)))<=($(vMaxDate))"}>, Day=} DS_Complete_Counter)

timmarsh
Contributor III
Contributor III
Author

Ah. No. vMaxDate is a variable calculated before cal formed.

Stupid me.


So...

The current month (of selection, or max record date if no selection) is expressed as below and now works.

=sum({$<Date = {">=$(=monthstart((Max(Date))))<=((Max(Date)))"}, Day=>} DS_Complete_Counter)

However!

The previous month is still not working. I am missing something simple. I tested monthstart and monthend in textboxes with and without selections and it's working fine, so I am missing something simple!

Its expression is now:

=sum({$<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))<=$(=monthend(addmonths(Max(Date),-1)))"}, Day=>} DS_Complete_Counter)

What am I missing?

Thanks for your help, Sunny.

Anonymous
Not applicable

Hi ,

Could you please try this expression.

=sum({$<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))<=$(=monthend(addmonths(Max(Date),-1)))"}>} DS_Complete_Counter).

sunny_talwar

Try this

=Sum({$<Date = {">=$(=MonthStart(Max(Date), -1))<$(=MonthStart(Max(Date), 0))"}, Day=>} DS_Complete_Counter)

Anonymous
Not applicable

I dont know why it is taking such a long time to moderate this.Qlik.PNG

timmarsh
Contributor III
Contributor III
Author

Hi Sunny,

Did you mean:

=sum({$<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))<=$(=monthend(addmonths(Max(Date),-1)))"}, Day=>} DS_Complete_Counter)

(I need to go back 1  month), also you still had the AddMonths -1 paramater in there but no add months.

to be clear, the current month is working perfectly in the state of no selections or with selections, and ignores day selection.

It's the previous month that is not selecting any records in the select state. In default state it works fine.

timmarsh
Contributor III
Contributor III
Author

Unfortunately, still nothing in the select state.

timmarsh
Contributor III
Contributor III
Author

This is the correct previous month expression.

I've no idea why the forcing of values of month, year and day to empty makes it correct.  I'd love to understand why!

=sum({$<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))<=$(=monthend(addmonths(Max(Date),-1)))"}, Day=, Year=, Month=>} DS_Complete_Counter)