topic Re: Flagging in set analysis in QlikView

Flagging in set analysis

qlikviewforum — Tue, 17 Nov 2015 07:44:12 GMT

Instead of flagging it as 1/0 I want to flag it as TRUE/FALSE. Any idea how this could be achieved?

Only problem here is the date range(from and to), else this could have been straight forward. Can anyone help me out on this?

=IF(

IF(Limit_Days='l_30',[30 days],

IF(Limit_Days='l_60',[60 days],

IF(Limit_Days='l_90',[90 days],

IF(Limit_Days='TBD',[60 days)

)

)<1

,sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 1),

sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 0))

Re: Flagging in set analysis

settu_periasamy — Tue, 17 Nov 2015 07:58:39 GMT

Hi,

As per my understanding, you want to show the text 'TRUE' instead of 1, right?

if so, you can try like..

,Replace(sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 1),1,'TRUE'),

Replace(sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 0)),0,'FALSE')

Re: Flagging in set analysis

jonathandienst — Tue, 17 Nov 2015 08:11:42 GMT

For true:

...sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} true()),...

and false:

...sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} false()))

Re: Flagging in set analysis

qlikviewforum — Tue, 17 Nov 2015 08:19:17 GMT

Good idea! it does did the trick but the problem it is showing all the dates except from and to date. Any idea what could be the issue? I am looking into that only. Please let me know if you found the issue.

Re: Flagging in set analysis

qlikviewforum — Tue, 17 Nov 2015 08:20:40 GMT

It is showing 0 for TRUE and -1 for FALSE. Where as I want to show TRUE/FALSE instead of 0/-1.

Re: Flagging in set analysis

settu_periasamy — Tue, 17 Nov 2015 08:53:53 GMT

Can you check your Set Expression Properly working or Not?

For Testing Purpose, Add one More expression, Put your Sum Expression.. Like

=sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 1)

don't give the label. Then Ok.

check, if the date value (From and To ) is correct or not? Like below screen shot..

Re: Flagging in set analysis

qlikviewforum — Tue, 17 Nov 2015 09:41:40 GMT

As you mentioned I have added a new expression to check whether the date range is correct or not. It does gives the expected result.

Re: Flagging in set analysis

settu_periasamy — Tue, 17 Nov 2015 09:57:24 GMT

Is it possible to post the sample?

Re: Flagging in set analysis

qlikviewforum — Tue, 17 Nov 2015 10:10:05 GMT

I know it would be easy if i can post the example but due to security reasons i cannot attach anything else i would have attached it.

Re: Flagging in set analysis

settu_periasamy — Tue, 17 Nov 2015 10:17:03 GMT

Ok. Let's give a try..

=Pick(Match(

IF( IF(Limit_Days='l_30',[30 days],

IF(Limit_Days='l_60',[60 days],

IF(Limit_Days='l_90',[90 days],

IF(Limit_Days='TBD',[60 days))))<1 ,

sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 1),

sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 0)),1,0),'TRUE','FALSE')

Re: Flagging in set analysis

qlikviewforum — Tue, 17 Nov 2015 10:46:25 GMT

Thanks a lot for your effort but that didn't work either. Same problem instead of showing only those dates which in available in the date range it is showing all the dates.

Re: Flagging in set analysis

qlikviewforum — Tue, 17 Nov 2015 11:32:15 GMT

Do you have any other alternative? Please share

Re: Flagging in set analysis

settu_periasamy — Tue, 17 Nov 2015 12:35:13 GMT

is your first mentioned expression working properly, which you posted in the first thread?

this one?

=IF(

IF(Limit_Days='l_30',[30 days],

IF(Limit_Days='l_60',[60 days],

IF(Limit_Days='l_90',[90 days],

IF(Limit_Days='TBD',[60 days)

)

)<1

,sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 1),

sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 0))

Re: Flagging in set analysis

qlikviewforum — Tue, 17 Nov 2015 12:40:05 GMT

Yes it is working perfectly. No issues with that.

The one which is posted is here one part of it. But in the future client wants something like mentioned in the below post. Once I am done with above I will go with the below one. Have added the URL for your reference.

Help required to create complicated chart

Re: Flagging in set analysis

settu_periasamy — Tue, 17 Nov 2015 13:00:53 GMT

Yes.. But without example it is difficult for me..

Re: Flagging in set analysis

qlikviewforum — Wed, 18 Nov 2015 06:08:05 GMT

Any other guesses?

Re: Flagging in set analysis

settu_periasamy — Wed, 18 Nov 2015 06:11:09 GMT

Hi,

As you already said, your expression is fine. if so, then Replace / Pick (Match .()) will also working fine..

No idea what's happened.

Re: Flagging in set analysis

qlikviewforum — Wed, 18 Nov 2015 10:29:03 GMT

At least you could help me out for below?

Help required to create complicated chart

Re: Flagging in set analysis

qlikviewforum — Wed, 18 Nov 2015 13:58:10 GMT

There was some issue in my expression. Your idea to use REPLACE worked for us. Could you please help me to create chart?

Help required to create complicated chart

The count that you see if basically the number of TRUE value for that particular month. Please see if you can help me out on the above as it is little urgent.