Sales where date =

[Anonymous]

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.

regards,

René

[swuehl]

Your set expression should look like

{<Month = {'Mai'} >}

[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

[swuehl]

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

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é

[swuehl]

Like

Count({<Products = {'Pizza'}>} Sales)

A Primer on Set Analysis

[swuehl]

Or

Count({<Products = {'*Pizza*'}>} Sales)

if you want to use LIKE %Pizza% style

[sunny_talwar]

Hi s Walter‌ -

Is this a new requirement? I would suggest creating a new thread for a new requirement. This will help you as you will get advice from more people and it will help this discussion be focused on one issue at time.

Thanks,

Sunny

[swuehl]

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).