Set expression issue when using modifiers

[#1]

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,

[venkatg6759]

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

[richard_pearce6]

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

[#4]

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,

[richard_pearce6 · Accepted solution]

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

[#6]

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

[richard_pearce6]

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

[#8]

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,

[richard_pearce6]

Hi Iav,

Using the flag

%Flag_IsThisDate ={1}

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

Regards

Richard

[#10]

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,

[richard_pearce6]

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