24 Replies Latest reply: Jun 30, 2014 12:17 PM by Richard Pearce RSS

    Set expression issue when using modifiers

    Lavanya Ramaswamy

      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 Pearce 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
          Tresesco B

          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
            Felim Shanaghy

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

            • Re: Set expression issue when using modifiers
              Richard Pearce

              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
                  Lavanya Ramaswamy

                  Hello Richard,

                   

                  Yes, the tables are linked on %KeyDate

                   

                  export.png

                   

                  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

                   

                  trend.png

                   

                  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 Pearce

                  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 Pearce

                      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
                        Lavanya Ramaswamy

                        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 Pearce

                            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
                                Lavanya Ramaswamy

                                Hello Richard,

                                 

                                Thank you very much for the detailed explanation.

                                 

                                I got  this working now

                                 

                                Rech exp.png

                                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
                                    Venkata Gogineni

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

                                    • Re: Set expression issue when using modifiers
                                      Richard Pearce

                                      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
                                          Lavanya Ramaswamy

                                          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:

                                          DateValueMTD Value
                                          6/1/20141818
                                          6/2/20142038
                                          6/3/20143068
                                          6/4/20142593
                                          6/5/201435128
                                          6/6/201427155
                                          6/7/201415170
                                          6/8/201417187
                                          6/9/201422209
                                          6/10/201428237
                                          6/11/201417254
                                          6/12/201419273
                                          6/13/201421294
                                          6/14/201424318
                                          6/15/201422340

                                           

                                           

                                           

                                          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 Pearce

                                              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
                                                  Lavanya Ramaswamy

                                                  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 Pearce

                                                      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
                                                          Lavanya Ramaswamy

                                                          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 Pearce

                                                              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
                                                                  Lavanya Ramaswamy

                                                                  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
                                              Lavanya Ramaswamy

                                              Hello Richard,

                                               

                                              This I want to show on my Dashboard Page. There are no selections and what I am focussing here is only on the current year.

                                               

                                              I have set up an alternate state for this sheet so that the other selections don't affect this sheet.

                                               

                                              What am I trying to do - Get the month's MTD - get the last month's MTD for a comparative period and plot the variance as a Graph.

                                               

                                              My Set Analysis expression reads

                                               

                                              // This year current mtd - this year last month to date comparative period
                                              =(sum({1<Fact_Year={$(=year(Today()))},%Flag_ThisMonthToDate={1},%Flag_ThisYear={1}>}[my Measure]))
                                              - (
                                              sum({1<Fact_Year={$(=year(Today()))} ,%Flag_PreviousMonthComparative_M01={1}  >}[my Measure]))

                                               

                                              My dimension in the table is Month Year.

                                               

                                              My problem is - as I am saying Fact_Year = Year(Today())  - In the case of Jan 2014 - it doesn't take dec 2013 as the last period's MTD and shows the MTD figure for Jan 2014 as variance.

                                               

                                              What would be your advise on this ?

                                               

                                              Also, I didn't see the problem as you mentioned in your previous post with this set expression - it seems to be bringing back the right figures.

                                               

                                              Thanks,

                                              Lav