topic get len of fields thats not in current selection in QlikView

get len of fields thats not in current selection

srinivasa1 — Mon, 13 Jun 2016 23:58:14 GMT

Hi Someone guide me how I can achieve make value of year month is 0 when its count
is less than 3( bcz Qtr is completed when Fiscal year and Month become 2016/06)

I have Fiscal year =2016

Fiscal year Qtr=2017 Q2

Fiscal year and Month = 2016/04

2016/05

2016/06 ( Not be selected as Qtr
is not started yet in that case I need to set flag as 0 otherwise 1

Thx

Re: get len of fields thats not in current selection

sunny_talwar — Tue, 14 Jun 2016 00:04:27 GMT

I am not certain what you trying to do. Would you be able to elaborate please?

Re: get len of fields thats not in current selection

srinivasa1 — Tue, 14 Jun 2016 00:10:44 GMT

Thanks Sunny for your reply I wanted set flag as 0 when fiscal year and month list not make qtr ( I mean we can see only two month for this qtr in list box in my example I will consider qtr only when it have 3 months ).Hope it helps

Re: get len of fields thats not in current selection

sunny_talwar — Tue, 14 Jun 2016 00:13:21 GMT

May be like this:

If(GetSelectedCount([fIscal qtr]) >= 3, 1, 0)

If you have selected 3 or more fiscal qtr, the expression will give 1, else it will 0

Re: get len of fields thats not in current selection

srinivasa1 — Tue, 14 Jun 2016 00:18:16 GMT

It looks good but I will not select fiscal year and month it based on Quarter selection below year and month fields will filtered and I want count that months fulfills as QTR or not

Re: get len of fields thats not in current selection

sunny_talwar — Tue, 14 Jun 2016 00:22:10 GMT

I am not sure what you are trying to do. Is there a sample you can share?

Re: get len of fields thats not in current selection

srinivasa1 — Tue, 14 Jun 2016 04:31:02 GMT

Hi Sunny,

Please find below sample screen.

thx

srini

Re: get len of fields thats not in current selection

tresB — Tue, 14 Jun 2016 04:44:38 GMT

May be like this?

=Sum(If(MonthEnd(max(total Date))>=Today(), 0, Sale))

Re: get len of fields thats not in current selection

srinivasa1 — Tue, 14 Jun 2016 05:14:13 GMT

Thanks Looks like it should work.

Re: get len of fields thats not in current selection

swuehl — Tue, 14 Jun 2016 09:03:06 GMT

I would suggest to create a flag in the script, based on your financial quarters, probably in a master calendar.

Something like

LOAD

DateField,

If( QuarterEnd(DateField,0, $(vFiscalYearStartMonth) ) > Today(1), 0, 1) AS [QuarterCompleted?],

...

Then in your chart:

Sum({<[QuarterCompleted] = {1}>} Sale)

Now you can also use this expression without any quarter selections, e.g. in a chart to show completed quarters with quarters as dimension.

Re: get len of fields thats not in current selection

srinivasa1 — Tue, 14 Jun 2016 15:24:20 GMT

Thanks swuehl.