9 Replies Latest reply: May 9, 2016 6:52 PM by Stefan Wühl RSS

    Sales where date =

    s Walter

      Hi,

       

      i want to show only sales from the the current date without filters. Wich expression must i use?

      I try this in the "sales measure" expression: Count({<Month = {"Mai">}} Sales), but it doesn´t work.

       

      2016-05-07_12h56_43.jpg

       

      regards,

      René

        • Re: Sales where date =
          Stefan Wühl

          Your set expression should look like

           

          {<Month = {'Mai'} >}

          • Re: Sales where date =
            Sunny Talwar

            s Walter mentioned that this need to based on current date, should this be based on a formula Stefan? Something like this

            Count({<Month = {'$(=Month(Today()))'}>} Sales)

             

            So when June comes, it shows the most Juni

              • Re: Sales where date =
                Stefan Wühl

                Maybe that's what he wants.

                 

                But if the data shows more than one year (currently not), it would be better to select on MonthYear, and clear selections in other calendar fields:

                 

                LOAD [Date],

                  [Month],

                  [Year],

                  [Week],

                  [Quarter],

                  MonthName(Date) as MonthYear

                FROM [lib://example-2/Calender.xlsx]

                (ooxml, embedded labels, table is Tabelle1);

                 

                Count({<MonthYear = {'$(=MonthName(Today()))'}, Date, Month, Year, Week, Quarter>} Sales)

                 

                Note that a similar set expression should probably applied to your second expression, too.

                 

                For a better introduction into point in time reporting, have a look at

                The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

                  • Re: Sales where date =
                    Sunny Talwar

                    Hahahaha ya, I was wondering why we want to see May-2015 data for May 2016. I was not sure for the exact reason. But just to add another possibility without needing to add the MonthName field would be this:

                     

                    Count({<Month = {'$(=Month(Today()))'}, Year = {$(=Year(Today))}>} Sales)

                     

                    Why create an extra field when we can do this with the existing ones

                      • Re: Sales where date =
                        s Walter

                        Thanks...

                         

                        I use "Products" as dimension. If i want only special products, how can i do it by an expression?

                         

                        Example:

                         

                        Products like "Pizza"

                         

                        regards,

                        René

                        • Re: Sales where date =
                          Stefan Wühl

                          Sunny T &lt;span class=&quot;icon-status-icon icon-mvp&quot; title=&quot;Mvp&quot;&gt;&lt;/span&gt; wrote:

                           

                          Hahahaha ya, I was wondering why we want to see May-2015 data for May 2016. I was not sure for the exact reason. But just to add another possibility without needing to add the MonthName field would be this:

                           

                          Count({<Month = {'$(=Month(Today()))'}, Year = {$(=Year(Today))}>} Sales)

                           

                          Why create an extra field when we can do this with the existing ones

                          Because you can fulfill this specific requirement with the two existing fields, but try to select two arbitrary months, like

                          Dec 2015 and Jan 2016 (it doesn't matter using set analysis or with two list boxes).

                           

                          Thus you don't need to create a new field now, but from experience, you will create it tomorrow.

                           

                          And I just try to avoid answering new threads tomorrow that I can answer in existing threads today

                           

                          Besides this, I still think that user selectable calendar fields should be cleared in the set expression, to avoid incompatible sets (unless the OP wants this chart not to show always the current month sales).