topic Exclude current/latest fiscal month year in QlikView

Exclude current/latest fiscal month year

sifatnabil — Tue, 28 Jul 2015 03:37:10 GMT

Hi all,

I have a graph like below, but since the month of July hasn't ended, I would like to exclude it.

I have 2 dimensions on this chart: FMonth (with values like Apr, May, Jun), and FYear (with values like 2014/2015, 2015/2016). Any ideas?

Re: Exclude current/latest fiscal month year

settu_periasamy — Tue, 28 Jul 2015 03:41:06 GMT

Hi,

i think you need to change the Previous Year (2014 - 2015) set expression. It shows the full Financial year value.

if possible, post the sample.

Re: Exclude current/latest fiscal month year

robert_mika — Tue, 28 Jul 2015 03:53:24 GMT

LIke this:

=if(month='Jul',0,sum(Sales))

Re: Exclude current/latest fiscal month year

robert_mika — Tue, 28 Jul 2015 03:57:38 GMT

or more dynamic:

=if(month=Month(Today()),0,sum(Sales))

Feeling Qlikngry?

How To /Missing Manual(18 articles)

Re: Exclude current/latest fiscal month year

senpradip007 — Tue, 28 Jul 2015 04:25:44 GMT

Try like this in expressions:

=if( MonthEnd(Today()) <> today(), 0, sum(Sales))

Re: Exclude current/latest fiscal month year

sifatnabil — Tue, 28 Jul 2015 05:29:01 GMT

Hi Robert_Mika‌, settu_periasamy‌, senpradip007‌, these won't work because my chart has 2 dimensions: FMonth (with values like Apr, May, Jun), and FYear (with values like 2014/2015, 2015/2016).

So I have to apply some logic on both these.

Re: Exclude current/latest fiscal month year

jagan — Tue, 28 Jul 2015 05:37:23 GMT

Hi,

Exclude it using set analysis

=Sum({<FMonth=, FYear=, DateFieldName={'>=$(=YearStart(Max(DateFieldName), -1, 4))<=$(=MonthEnd(max(DateFieldName), -1))'}>} Sales)

Replace DateFieldName with your actual date field name.

Hope this helps you.

Regards,

Jagan.