topic Re: Count of Distinct managers for max monthyear with more than 5 direct report in QlikView

Count of Distinct managers for max monthyear with more than 5 direct report

Anonymous — Fri, 10 Jun 2016 08:26:22 GMT

Hi All,

Please let me know what's wrong with below expression:

Count({<[MANAGER GEID] = {"=Count([DRs All])>5"},MonthYear = {"$(=max(MonthYear))"} >} Distinct [MANAGER GEID])

We have to select max monthyear to get accurate count, but I want to display right count by default for max monthyear.

Thanks

Neetha

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Kushal_Chawda — Fri, 10 Jun 2016 08:31:52 GMT

try to give format to your month year

Assuming your monthyear date format is 'MMM-YYYY'

Count({<[MANAGER GEID] = {"=Count([DRs All])>5"},MonthYear = {"$(=Date(max(MonthYear),'MMM-YYYY'))"} >} Distinct [MANAGER GEID])

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Anonymous — Fri, 10 Jun 2016 08:42:19 GMT

Hi Kushal,

Still issue persists, I have to select max monthyear.

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Kushal_Chawda — Fri, 10 Jun 2016 08:45:08 GMT

can you show the screenshot of MonthYear field in list box?

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Anonymous — Fri, 10 Jun 2016 08:47:49 GMT

Hi,

Its same as you guessed - May-2016

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Digvijay_Singh — Fri, 10 Jun 2016 08:50:15 GMT

try to see the result of expression at straight table header, it may show you that month year is not properly compared.

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Anonymous — Fri, 10 Jun 2016 09:03:29 GMT

Hi Digvijay,

when I look at straight table header it still gives right Max MonthYear,but still I have to select max monthyear

Re: Count of Distinct managers for max monthyear with more than 5 direct report

sasikanth — Fri, 10 Jun 2016 09:14:06 GMT

Try something like ,

Month_Year={"$(=Date(max(Date_Field),'MMM-YYYY'))"}>}

Re: Count of Distinct managers for max monthyear with more than 5 direct report

MK_QSL — Fri, 10 Jun 2016 09:19:46 GMT

Try this...

=Count({<[MANAGER GEID] = {"=Count({<MonthYear = {"$(=Date(max(MonthYear),'MMM-YYYY'))"}>}[DRs All])>5"},MonthYear = {"$(=Date(max(MonthYear),'MMM-YYYY'))"} >} Distinct [MANAGER GEID])

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Kushal_Chawda — Fri, 10 Jun 2016 09:26:29 GMT

just wanted to know if it is not string

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Anonymous — Fri, 10 Jun 2016 09:26:31 GMT

Hi Manish,

I was trying to do this expression also and getting below error:

Error: Error in set modifier ad hoc element list:',' or ')' expected

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Anonymous — Fri, 10 Jun 2016 09:40:45 GMT

Hi Kushal,

thanks for try to help

Max MonthYear is working for other measures, so it should work for this measure also isn't it.

correct me if I am wrong.

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Kushal_Chawda — Fri, 10 Jun 2016 09:52:08 GMT

try this

Count({<[MANAGER GEID] = {"=Count({<MonthYear = {"$(=Date(max(MonthYear),'MMM-YYYY'))"}>}[DRs All])>5"}>} Distinct [MANAGER GEID])

Re: Count of Distinct managers for max monthyear with more than 5 direct report

MK_QSL — Fri, 10 Jun 2016 09:53:05 GMT

Use this

=COUNT({<[MANAGER GEID] = P({1<[MANAGER GEID] = {"=COUNT({<MonthYear = {'$(=Date(Max(Date#(MonthYear,'MMM-YYYY')),'MMM-YYYY'))'}>}[DRs All])>5"}>}[MANAGER GEID])>}DISTINCT [MANAGER GEID])

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Anonymous — Fri, 10 Jun 2016 10:05:51 GMT

output is 0(Zero)

Re: Count of Distinct managers for max monthyear with more than 5 direct report

MK_QSL — Fri, 10 Jun 2016 10:16:00 GMT

You can share you app now... This discussion will go on and on and would never finish... Better we can directly work on sample app.

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Anonymous — Fri, 10 Jun 2016 10:23:55 GMT

Hi,

Getting same error:

Error: Error in set modifier ad hoc element list:',' or ')' expected

Re: Count of Distinct managers for max monthyear with more than 5 direct report

Anonymous — Fri, 10 Jun 2016 10:25:50 GMT

Thanks Manish and Kushal.

wrong assumption on my part if its working in other expressions it should work here also.

Below expression solved my Issue with your help:

=Count({<[MANAGER GEID] = P({1<[MANAGER GEID] = {"=COUNT({<MonthYear = {'$(=Max(Date(MonthYear,'MMM-YYYY')))'}>}[DRs All])>5"}>}[MANAGER GEID])>} Distinct [MANAGER GEID])

Re: Count of Distinct managers for max monthyear with more than 5 direct report

MK_QSL — Fri, 10 Jun 2016 10:29:57 GMT

Great that you got solution for your problem..!