topic Re: Need the condition on setanalysis in QlikView

Need the condition on setanalysis

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

Hi Team,

I need a condition on set analysis based on the condition Total Met/Total resolved for that particular month

Here condition for Total Met =(lastresolveddate<=Targetdate) then count of IDs

Total resolved = count(lastresolveddate)

Note: Exclude records if any one of the dates is missing or null

Thanks,

Chinnu

Re: Need the condition on setanalysis

sunny_talwar — Mon, 26 Sep 2016 15:34:00 GMT

May be this:

Count({<ID = {"=lastresolvedate <= Targetdate"}>}ID)

Re: Need the condition on setanalysis

chinnu123 — Mon, 26 Sep 2016 16:04:32 GMT

Hi sunny, thanks for the quick reply, expected to exclude missing dates in any of the date columns but it is counting all records.

Re: Need the condition on setanalysis

sunny_talwar — Mon, 26 Sep 2016 17:27:31 GMT

May be this:

Count({<ID = {"=lastresolvedate <= Targetdate and (Len(Trim(lastresolvedate )) > 0 and Len(Trim(Targetdate)) > 0)"}>}ID)

Re: Need the condition on setanalysis

chinnu123 — Mon, 26 Sep 2016 20:22:34 GMT

Hi Sunny,

Thanks for your help Its working ...I used the below expression

Count(DISTINCT{<ID={"=lastresolveddate<=Targetdate"}>}ID)

/count({<lastresolveddate={"*"},Targetdate={"*"}>}ID)

In the same way I need to write current fisical year I.e April 2016 to next year March 2017

For this I am using below expression But unable to fetch correct output Please correct me If I am wrong in the expression..

=(if(isnull(above((count(DISTINCT{<lastresolveddate={"=lastresolveddate<=Targetdate"}>} ID)))),

(count(DISTINCT{<lastresolveddate={"=lastresolveddate<=Targetdate"}>}ID)),

above((count(DISTINCT{<lastresolveddate={"=lastresolveddate<=Targetdate"}>} ID)))

+ (count(DISTINCT{<lastresolveddate={"=lastresolveddate<=Targetdate"}>}ID))))

/(if(isnull(above(count(distinct {<lastresolveddate={"*"},Targetdate={"*"}>}ID))),

count(DISTINCT{<lastresolveddate={"*"},Targetdate={"*"}>}ID),

above(count(DISTINCT{<lastresolveddate={"*"},Targetdate={"*"}>}ID))

+count(DISTINCT{<lastresolveddate={"*"},Targetdate={"*"}>}ID)))

Thanks,

Chinnu

Re: Need the condition on setanalysis

sunny_talwar — Mon, 26 Sep 2016 20:26:15 GMT

This is complicated stuff, can you share a sample

Re: Need the condition on setanalysis

chinnu123 — Mon, 26 Sep 2016 23:47:59 GMT

Hi Sunny,

Here I am sharing sample qvw file and sample data for your reference.

In the shared qvw file I am able to find Month with your help.

But I am unable to get correct output for YTD.

For your reference

If we click on the AXpprt we need to get 99% for august Month for YTD also..

Hope It helps.

Thanks,

Chinnu.

Re: Need the condition on setanalysis

sunny_talwar — Tue, 27 Sep 2016 07:49:41 GMT

What are the YTD percentages you expect to see for other months?

Re: Need the condition on setanalysis

chinnu123 — Tue, 27 Sep 2016 14:02:42 GMT

Hi Sunny,

The YTD percentages for other months would be

April-100%

May-99%

June-97% etc...