topic Re: Pivot Table - Show Current Month in QlikView

Pivot Table - Show Current Month

joshrussin — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Pivot Table - Show Current Month

sunny_talwar — Thu, 20 Jul 2017 17:06:21 GMT

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

Re: Pivot Table - Show Current Month

joshrussin — Thu, 20 Jul 2017 17:10:41 GMT

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?

Re: Pivot Table - Show Current Month

joshrussin — Thu, 20 Jul 2017 17:30:04 GMT

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)

Re: Pivot Table - Show Current Month

steverosebrook — Thu, 20 Jul 2017 19:07:15 GMT

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)

Re: Pivot Table - Show Current Month

joshrussin — Thu, 20 Jul 2017 19:15:26 GMT

This returned a 0.

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

Re: Pivot Table - Show Current Month

steverosebrook — Thu, 20 Jul 2017 19:23:49 GMT

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.

Re: Pivot Table - Show Current Month

Anonymous — Thu, 20 Jul 2017 19:28:04 GMT

‌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

Re: Pivot Table - Show Current Month

joshrussin — Thu, 20 Jul 2017 19:33:03 GMT

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

Re: Pivot Table - Show Current Month

steverosebrook — Thu, 20 Jul 2017 19:43:55 GMT

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"?

Re: Pivot Table - Show Current Month

joshrussin — Thu, 20 Jul 2017 20:06:31 GMT

Thanks! Works perfectly now!

Re: Pivot Table - Show Current Month

joshrussin — Thu, 20 Jul 2017 21:19:05 GMT

Now I have another question for ya.

I have put the same equation in a line graph and am getting a random number hanging on the end after all of the correct dates are displayed. any clue how to get rid of this?

Re: Pivot Table - Show Current Month

joshrussin — Thu, 20 Jul 2017 21:21:39 GMT

Never mind, I just unchecked the null value option. Works perfectly.