topic Set expression issue when using modifiers in QlikView

Set expression issue when using modifiers

Mon, 26 Jan 2026 18:19:17 GMT

This is my first app on QV and have been trying to learn QV as I develop.

I have been trying to use the master calendar (http://community.qlik.com/docs/DOC-6593) by @Richard.Pearce60 in my app and the expressions for MTD/ MTD prev. period don't seem to work.

I have loaded the master calendar from a qvd and the fact table from a qvd

I am not sure where am going wrong and would appreciate any pointers

=sum({<Date={'$(=max(Date))'},%Flag_ThisMonthToDate={1} >} Measure_Name) is what I am using for MTD numbers

Would appreciate any pointers

Thanks,

Re: Set expression issue when using modifiers

tresB — Tue, 17 Jun 2014 08:27:02 GMT

I guess, Date={$(=Max(Date))} portion should not be there in the set. Try without it and check the result.

=sum({<%Flag_ThisMonthToDate={1} >} Measure_Name)

Re: Set expression issue when using modifiers

Tue, 17 Jun 2014 08:33:19 GMT

=sum({<%Flag_ThisMonthToDate={1},Date={'>=$(=MonthStart(Max(Date)))<=$(=Max(Date))'} >}Recharge_AED)

Re: Set expression issue when using modifiers

richard_pearce6 — Tue, 17 Jun 2014 08:59:48 GMT

Hi Iav,

Your expression looks good. If the 17 June 2014 is the max Date you will return %KeyDate's 1st June to 17th of June 2014.

What selections do you have in your dashboard?

Have you linked your Calendar to the Fact table via the %KeyDate?

Regards

Richard

Re: Re: Set expression issue when using modifiers

Tue, 17 Jun 2014 10:23:01 GMT

Hello Richard,

Yes, the tables are linked on %KeyDate

What I am trying to achieve is a chart with Day as a dimension and three expressions for MTD, MTD prev month, MTD for the same month last year for a particular month selected and in case a date is selected it should bring back the MTD figures for that day

In the above case my selection is May 25th 2014 and it shows the MTD numbers for May 2014, the numbers for April 2014 (comparative period which is 1 - 25th) and for May 2013.

Well, I think that is where my problem lies - in this sample data set I have data only until 7th June. So in this case my expression is failing I guess.

Kindly advise.

Thanks

Re: Set expression issue when using modifiers

richard_pearce6 — Tue, 17 Jun 2014 10:57:28 GMT

Hi Iav

Its a very good use of the calendar!

Yes if you haven't got data after June 7th I'd expect your blue line to end on day 7. The other lines would continue until the 17th day (presuming you have a complete history up to the 7th of June).

You could perhaps modify the expression to compensate.

One solution could be to add an additional Date field in your fact table (same format as Date, DD-MMM-YYY) and use RangeMax() on both the fields.

SET a variable

SET vMaxDate = date(rangemax(Date,NewFactDate),'DD-MMM-YYYY');

Expression

num(sum({<Date={'$(=vMaxDate)'},%Flag_ThisMonthToDate={1} >} Sales),'0,000')

Regards

Richard

Re: Set expression issue when using modifiers

richard_pearce6 — Tue, 17 Jun 2014 11:15:56 GMT

Just thought this won't work as it will still get the max date from the calendar.....

The basic principle is correct but the variable needs a re-think. Just popping for lunch so will have a look when I'm back

Rich

Re: Set expression issue when using modifiers

Tue, 17 Jun 2014 11:16:13 GMT

Hello Richard,

Sorry if am sounding dumb - but could you please elaborate this for me so that its a little more clear.

From your post I understand that if I need create a another date in my Fact table - that NewFactDate is essentially the same as creating a field Date as NewFactDate.

so it would translate to

Load

Date,

Date as NewFactDate, .. ... ...

from

my fact qvd;

Then create the vMaxDate variable which chooses the maximum date between Date and NewFactDate.

Assuming All my selections are based on Date field - I choose May 25th 2014

What would be vMaxDate *confused here*

Also, in my fact I have data historically and I don't want the lines going until 17th but as in my previous example only until the day I have chosen.

On an other note, I have multiple fact tables to be used and didn't want to go the way of creating a calendar for each and hence I found your method wonderful. The introduction of weekday/weekend/ holiday flag will be something which I hope to use to awe my business user community.

Thanks for your help in advance.

Re: Set expression issue when using modifiers

richard_pearce6 — Tue, 17 Jun 2014 12:19:34 GMT

Hello,

These extra steps I'm describing are just to resolve your missing data issue. That is if the calendar allows a higher date being selected when there's no corresponding fact data....

In the expression using Date={'$(=max(Date))'} ensures the set analysis is referencing a single date. So if you select the [Month] = May 2014 the max(Date) is 31-May-2014.

Of course you could limit your Calendar QVD load just to %KeyDates that exist in your fact tables using where Exists(%KeyDate) although this may not suit your needs as fact tables may not contain an identical set. For example some may have the 7th June as the most recent and some may have the 16th....

The way I'm describing Is basically ensuring the Date referenced in Set Analysis is never higher than the FACT data

I had a think and if the variable is changed to this it will work:

SET vMaxDate = date(RangeMin(max(Date),max(NewFactDate)),'DD-MMM-YYYY');

Basically we're taking the Max Date from both tables; in our example it would be 17-Jun and 7-Jun from calendar and fact tables respectively and taking the min of both (RangeMin()) which would be the 7-Jun. We now use this date contained in the variable in the expression.

sum({<Date={'$(=vMaxDate)'},%Flag_ThisMonthToDate={1} >} [Your Field to Sum])

Now vMaxDate = 7-Jun-2014 the chart will show three lines until the 7th day for their respective months (if this is the result you were aiming for)

Of course this could be confusing for the user. They have selected the 17th but can only see up to the 7th. You can place objects on the sheet that helps them understand why this is.

If you have different dates in each of your fact tables (such as Sales Date, Invoice Date, Payment Date) this may help http://community.qlik.com/docs/DOC-6502 and can be used in conjunction with this calendar.

I hope this helps

Richard

Re: Set expression issue when using modifiers

Tue, 17 Jun 2014 19:43:12 GMT

Hello Richard,

Thank you very much for the detailed explanation.

I got this working now

There a few other things I am trying to do - one of which is trying to use this to produce a chart which has daily numbers similar to the above. The above is MTD numbers as you can see for which I use the expression as below.

sum({<Date={'=$(=max(Date))'},%Flag_PreviousMonth_M01={1} >}[My Measure])

If I use the %Flag_IsThisDate - then am able to plot for the chosen month

sum({$<Date={'=$(=max(Date))'},%Flag_IsThisDate={1} >}[My Measure])

But what I would like to achieve is if the month chosen is June 2014, then a daily trend graph as above for the periods of May 2014, April 2014 and June 2013.

Any pointers ?

Thanks,

Re: Set expression issue when using modifiers

venkatg6759 — Tue, 17 Jun 2014 19:48:06 GMT

sum({$<Date={'>=$(=monthstart(addmonths(max(Date),-3)))',%Flag_IsThisDate={1} }>}[My Measure])

Re: Set expression issue when using modifiers

richard_pearce6 — Wed, 18 Jun 2014 07:40:55 GMT

Morning Iav,

Good news its working now for you,

I'm not sure if I understand your latest question. If you have a month selected then %Flag_IsThisDate={1} will return all Point In Time flags for that month.

If you wanted the previous month then %Flag_PreviousMonth_M01={1} will give you May-2014 (assuming you have June-2012 selected).

I believe there may be more to your question, could you give me more information?

Thanks

Richard

Re: Set expression issue when using modifiers

Wed, 18 Jun 2014 10:31:19 GMT

Hello Richard,

It was way past midnight and hence I think I was composing this half asleep.

Yes, you are indeed right, the charts I have are point of time and not cumulative.

I am trying a few different things

What I want to FINALLY achieve is this

2 charts - one which shows daily numbers for a particular measure and one which shows MTD numbers for a particular measure for the period chosen

So the dimension would be day - and the user would choose the month

In that case chart 1 should show Measure - daily numbers for M, M-1, M-2 and M (Y-1). Chart 2 should show Measure - as MTD (cumulative) numbers for the periods M, M-1, M-2 and M (Y-1)

Eg:

Date	Value	MTD Value
6/1/2014	18	18
6/2/2014	20	38
6/3/2014	30	68
6/4/2014	25	93
6/5/2014	35	128
6/6/2014	27	155
6/7/2014	15	170
6/8/2014	17	187
6/9/2014	22	209
6/10/2014	28	237
6/11/2014	17	254
6/12/2014	19	273
6/13/2014	21	294
6/14/2014	24	318
6/15/2014	22	340

The other things I am trying to do are http://community.qlik.com/message/550761#550761 which I would come to next after I sort this out.

Thanks,

Re: Set expression issue when using modifiers

richard_pearce6 — Wed, 18 Jun 2014 13:28:34 GMT

Hi Iav,

The first set on numbers should be simple enough. Using the Flags we've discussed previously.

Cumulative numbers are a bit special and aren't covered by the Calendar. There are a number of threads on the subject but ultimately you need to use a formula which will look something like this:

=RangeSum( above( sum( "Here put the normal Calendar Flags, Calculation Field" ),0,rowno() ))

Richard

Re: Set expression issue when using modifiers

Thu, 19 Jun 2014 08:29:26 GMT

Good morning Richard,

Thank you for your wise words. I have been able to get both the trends up and running

If I may bother you a little more and pick your brains - I want a chart with 12 months rolling windows to be showing the cumulative number for every month.

So the chart dimension is on Month-Year (from Calendar) and then I want to show the monthly cumulative number for the last 12 months when a month is chosen. Should I write 12 expressions to bring back every month ?

How would you advise to approach this ?

Thanks,

Lav

Re: Set expression issue when using modifiers

richard_pearce6 — Thu, 19 Jun 2014 08:55:06 GMT

Morning Iav,

If you're chart dimension is Month Year you could use %Flag_LastMonth_M12 in a single expression.

This flag will give you all KeyDates from the Max(Date) selected for the last 12 months. It will include any part months. If this is an issue you could create a new flag (using ones already there as starting points) or you could change the Date={'$(=max(Date))'} element to Date={'$(=MonthsEnd(max(Date)))'}

Regards

Richard

Re: Set expression issue when using modifiers

Tue, 24 Jun 2014 16:34:52 GMT

Hello Richard,

Thank you. Sorry I disappeared for a bit .

I have a question, regarding the use of master calendar.

When I use the master calendar it gets joined to my fact table based on the key date. So this works fine in the case I want to do point of time reporting on trends etc. So the user chooses a month and I give them the relevant charts

There are cases where I also want to let the user choose more than a month and just show the daily numbers/ month to date (cumulative) number for those two months (or the chosen months/ weeks/ days) - How can I achieve this ?

And the other case is that I just want to show the current month's month-to-date cumulative number irrespective of the choice the user makes - how should I go about this ?

Thanks in advance,

Re: Set expression issue when using modifiers

richard_pearce6 — Wed, 25 Jun 2014 09:16:59 GMT

Hi Iav,

Using the flag

%Flag_IsThisDate ={1}

Will make a 1-2-1 connection between the Date and %KeyDate

Regards

Richard

Re: Set expression issue when using modifiers

Wed, 25 Jun 2014 13:06:17 GMT

Hello Richard,

Using that flag I would be able to show the current year's month-to-date cumulative number irrespective of the choice the user makes

I also tried this to get the current year's MTD. I created a variable vFactYear in the fact table which is Year(Transaction_date) and used that in the following expression

sum({1<vfactyear={$(=year(Today()))},%Flag_ThisMonthToDate={1},%Flag_ThisYear={1}>}[my measure])

The above works fine and I get the MTD figures for every month until June 25th for 2014.

Now I want to get the MTD figured for every month until June 25th for 2013.

I used this sum({1<vFactYear={$(=year(Today())-1)},%Flag_ThisMonthToDate={1},%Flag_ThisYear={1}>}[my measure]) but this brings back until 201312.

The above aside the second issue is

"There are cases where I also want to let the user choose more than a month and just show the daily numbers/ month to date (cumulative) number for those two months (or the chosen months/ weeks/ days) - How can I achieve this ? "

I am going to try and see if I can use the same flag for the above case as well.

Thanks,

Re: Set expression issue when using modifiers

richard_pearce6 — Wed, 25 Jun 2014 13:42:45 GMT

Hi Ian,

Looking at your second expression I think you need to change vFactYear={$(=year(Today())-1)} to use the Year field from the calendar.

If field vFactYear is in your fact table it will still link to all dates in your calendar table due to the Cartesian product element

Richard