Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
chinnu123
Creator
Creator

Need the condition on setanalysis

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

8 Replies
sunny_talwar

May be this:

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

chinnu123
Creator
Creator
Author

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

sunny_talwar

May be this:

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

chinnu123
Creator
Creator
Author

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

sunny_talwar

This is complicated stuff, can you share a sample

chinnu123
Creator
Creator
Author

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.

sunny_talwar

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

chinnu123
Creator
Creator
Author

Hi Sunny,

The YTD percentages  for other months would be

April-100%

May-99%

June-97%  etc...