Pivot Table - Show Current Month

[joshrussin]

Trying to figure out how to make this pivot table work correctly.

I am trying to get ONLY the days in the CURRENT month in this pivot table. Any ideas?

Right now I have a column = Date

I have tried setting the column = {$<Month_RD = {"$(=Month(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"},  Year_RD = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}>}Date

But it says that is invalid.

[sunny_talwar]

Are you using the above as a dimension? I think you need to use this as a set analysis within your measures

[joshrussin]

I have it as a dimension because I need to see the individual totals per client for every day of the month. Do you have any other suggestions?

[joshrussin]

If I use this expression, it will show the last 10 days.

vDays_Con = 12

currently there are 12 business days consumed this month. If I could make this equation account for business days and holidays, it would work!

=if(Date >= today()-vDays_Con and Date <= today(), Date)

[steverosebrook]

I typically create a dimension with an if/then that returns the dates you want, or a null value.  Then you just need to uncheck the "Include null values" box on the dimension.

Here's code that I think might work:

=if(year(Month_RD)=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays)))

    and

    month(Month_RD)=month(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))),date(Month_RD,'M/D/YYYY'),1/0)

[joshrussin]

This returned a 0.

Why is there a 1,0 on the end of that?

[steverosebrook]

It's actually a 1 divided by 0 (1/0).  It's the best way I've found to create a null value.  Qlik is a little quirky when it comes to nulls, I've found. 

[Anonymous]

‌You have to limit the data in expression. Let Date be the dimension and in expressions

you can do something like this

Date >= Monthstart(Max(Date)) and Date <= Monthend(Max(Date)).

You will have to use se analysis to build this expression

[joshrussin]

Using that expression, nothing generates with "include null values" checked,

if I uncheck it, Then it just shows the total for the month, I believe. I need each individual day showing not just one column

[steverosebrook · Accepted solution]

Sorry, instead of "Month_RD", you need to replace that with whatever date field has your dates in it.  I guess maybe you said that was a field called "Date"?