topic Re: Defaulting to Current Month with option to Change Month in QlikView

Defaulting to Current Month with option to Change Month

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

Qlik World,

I have an expression for a simple bar chart: sum({<LastMonth={1}, BUDGET_ACTUALS={'BUDGET'}>} HEADCOUNT)

This displays the budget of the previous month's headcount, but I am wanting to have it be where the deafult remains the same and allows for past months to also display.

Two part question:

How can I edit this expression to allow for this? I already have a month list box on the app, so that part is already done for once I can select a past month.
Is there a way to make the title of the object dynamic? It is currently erroneously called Current Month Headcount since it defaults to that view, but once I select a past month, I would like for the title to dynamically become Monthly Headcount.

Regards and many thanks,

Re: Defaulting to Current Month with option to Change Month

Anil_Babu_Samineni — Tue, 08 May 2018 00:23:52 GMT

Perhaps this using Title as measure

='You selected' & Concat(Chr(39) & Month & Chr(39), ',') & 'and the count is' & Count(Month)

Re: Defaulting to Current Month with option to Change Month

Anonymous — Tue, 08 May 2018 12:46:12 GMT

I'll give that a try and get back to you real soon, Anil. Thanks!

Re: Defaulting to Current Month with option to Change Month

Anonymous — Tue, 08 May 2018 13:13:05 GMT

Anil, that worked for the dynamic title component, very much appreciated. Any advice on the other part regarding having the current month display as the default view with the option to change it with the month selection?

Re: Defaulting to Current Month with option to Change Month

Anil_Babu_Samineni — Tue, 08 May 2018 13:24:02 GMT

Perhaps this?

=If(GetFieldSelections(Month)>0, 'Current Month headcount', 'You selected' & Concat(Chr(39) & Month & Chr(39), ',') & 'and the count is' & Count(Month))

Re: Defaulting to Current Month with option to Change Month

Anonymous — Tue, 08 May 2018 13:46:07 GMT

That hasn't worked yet, but I am trying to replace a few terms at the moment and will let you know how it goes. Thanks!

Re: Defaulting to Current Month with option to Change Month

Anil_Babu_Samineni — Tue, 08 May 2018 13:48:32 GMT

If above one replicate your issue, I think the one which latest should work. May be inter place from last to first like

=If(GetFieldSelections(Month)>0, 'You selected' & Concat(Chr(39) & Month & Chr(39), ',') & 'and the count is' & Count(Month), 'Current Month headcount')

=If(GetFieldSelections(Month)>0, 'You selected' & Concat(Chr(39) & Month & Chr(39), ',') & 'and the count is' & Count(Month), Your Default Measure)

Re: Defaulting to Current Month with option to Change Month

Anonymous — Tue, 08 May 2018 15:17:13 GMT

This ended up working for me:

if(isnull(GetCurrentSelections()),sum({<CurentMonth={1}, BUDGET_ACTUALS={'BUDGET'}>} HEADCOUNT), sum({<BUDGET_ACTUALS={'BUDGET'}>} HEADCOUNT))

Re: Defaulting to Current Month with option to Change Month

Anonymous — Tue, 08 May 2018 21:29:46 GMT

There is a similar table with the same filter issue. We have been able to apply some of the solutions that have been suggested here to this table, but we have some issues.

We would like to display Overhead Details with the below columns shown here with no filters applied:

However, we need to be able to filter by month or quarter. As some of the columns are month-specific, we are not sure how to modify the table to display relevant data.

Notice that the current month actual below is the same as the current quarter actual when we have selected to filter by a fiscal month. The current quarter actual needs to include January-March, not only January as it shows now. Currently, if a quarter were selected, then the actuals and the budget display fine.

Here is our current expression for the current quarter budget:

Re: Defaulting to Current Month with option to Change Month

Anonymous — Wed, 16 May 2018 16:07:31 GMT

Here is the expression for column "Cur Qtr Act":

if(isnull(GetCurrentSelections()), sum({<CurrentQuarter={1}, BUDGET_ACTUALS={'BUDGET'}>}DOLLARS)/1000, sum({<BUDGET_ACTUALS={'BUDGET'}>}DOLLARS)/1000)

It currently returns the same data as "Cur Mon Act" but it should be returning the cumulative actuals for all three months in a given

quarter.

Here is how we define Current Quarter in the data model:

if(ceil([Fiscal Month Number]/3)= ceil(Month(today())/3), 1, 0) as CurrentQuarter,

Thoughts?