topic Re: Set Analysis Expression is not working in QlikView

Set Analysis Expression is not working

Anonymous — Mon, 29 Aug 2016 11:25:54 GMT

Hi all,

I want to write this if expression in Set Analysis -

IF Expression:

(count(distinct if(PostingAction = 'ADD' and ExpiresDate>=AsOfDate, JobBoardJobId))) (Expression is working fine)

Set Analysis Expression :

count({<PostingAction = {'ADD'} , ExpiresDate={">=AsOfDate"}>} DISTINCT JobBoardJobId) (Expression is not working)

I am trying to implement the same in Set Analysis but its not working.

Note : AsOfDate is a field name. In Data Model, AsOfDate field is in one table , Expires Date and Action Date is in other table. Hence couldn't create a flag in data model in this case. That's why trying to implement it using Set Analysis on Front End.

Can anyone help me where I am doing wrong

Regards,

Nisha

Re: Set Analysis Expression is not working

sunny_talwar — Mon, 29 Aug 2016 11:28:06 GMT

Is AsOfDate a variable or fieldname? If it is a field, then it seems you are looking for a row wise evaluation which isn't going to work for you because set analysis is evaluated once per chart.

Re: Set Analysis Expression is not working

Anonymous — Mon, 29 Aug 2016 11:30:15 GMT

may be try like this at script?

Load

If(ExpiresDate>=AsOfDate,1,0) as Flag

From Table;

then write this expression:

=count({<PostingAction = {'ADD'} , Flag={'1'}>} DISTINCT JobBoardJobId)

Re: Set Analysis Expression is not working

Anonymous — Mon, 29 Aug 2016 11:30:30 GMT

I have updated my post now. As Of Date is a field name.

Re: Set Analysis Expression is not working

Anonymous — Mon, 29 Aug 2016 11:31:03 GMT

AsOfDate is a field name. In Data Model, AsOfDate field is in one table , Expires Date and Action Date is in other table. Hence couldn't create a flag in data model in this case. That's why trying to implement it using Set Analysis on Front End.

Re: Set Analysis Expression is not working

Anonymous — Mon, 29 Aug 2016 11:35:00 GMT

may be like this?

Set DateFormat='DD/MMM/YYYY'; in script

Set Analysis Expression :

count({<PostingAction = {'ADD'} , ExpiresDate={">=$(=date(AsOfDate))"}>} DISTINCT JobBoardJobId)

make sure both should have same format!

Re: Set Analysis Expression is not working

sunny_talwar — Mon, 29 Aug 2016 11:35:24 GMT

Based on your description, you will probably need to stick with if statement. May be like this:

Count(DISTINCT I{<PostingAction = {'ADD'}>} If(ExpiresDate>=AsOfDate, JobBoardJobId))

Re: Set Analysis Expression is not working

Anonymous — Mon, 29 Aug 2016 11:48:44 GMT

No, Its not working when it compares row wise Dates. Thanks for your suggestion.

If I select one date, that time it will work.