topic Trouble with dates and current selections in set expression in QlikView

Trouble with dates and current selections in set expression

timmarsh — Wed, 17 Jan 2018 01:58:45 GMT

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.

Re: Trouble with dates and current selections in set expression

sunny_talwar — Wed, 17 Jan 2018 02:10:06 GMT

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)

Re: Trouble with dates and current selections in set expression

timmarsh — Wed, 17 Jan 2018 03:16:37 GMT

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.

Re: Trouble with dates and current selections in set expression

Anonymous — Wed, 17 Jan 2018 06:13:20 GMT

Hi ,

Could you please try this expression.

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

Re: Trouble with dates and current selections in set expression

sunny_talwar — Wed, 17 Jan 2018 09:27:18 GMT

Try this

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

Re: Trouble with dates and current selections in set expression

Anonymous — Wed, 17 Jan 2018 10:11:47 GMT

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

Re: Trouble with dates and current selections in set expression

timmarsh — Wed, 17 Jan 2018 22:27:37 GMT

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.

Re: Trouble with dates and current selections in set expression

timmarsh — Wed, 17 Jan 2018 22:29:12 GMT

Unfortunately, still nothing in the select state.

Re: Trouble with dates and current selections in set expression

timmarsh — Wed, 17 Jan 2018 22:31:29 GMT

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)