topic Re: How to summarize the column for a table in set Analysis in QlikView

How to summarize the column for a table in set Analysis

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

Hi Experts,

I have requirement to show current openings till date in KPI. For that i want use these fields in my set expression

Summarize column is jobid

Date Column is EnterDate

Max(No.of Openings) i want to show

in simple& normal way like this:CALCULATE(SUMX(SUMMARIZE(PlacementDate,PlacementDate[jobid] ),CALCULATE(MAX(PlacementDate[NoOpenings]))),

For that i tried some thing like this way in our Qlik:

=Aggr(Max(TOTAL <jobid> EnterDate){<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},DATETYPE={'EnterDate'},JobClassid={4}>}NoOpenings))

‌

Note: I have multiple date columns for that i have linked with master calender

Can you guys please help me out for this

bhasker.smu‌priyalvp24‌manoj217‌loveisfail‌rwunderlich‌rajivmeher‌

Thank you in advance

Re: How to summarize the column for a table in set Analysis

sunny_talwar — Fri, 07 Sep 2018 10:59:47 GMT

Would you be able to share some sample data and the output you are expecting to see from it?

Re: How to summarize the column for a table in set Analysis

bvssudhakar — Fri, 07 Sep 2018 11:09:30 GMT

Hi Sunny,

Here I am attaching my sample qvw.

I want create a text object for current openings till date available. What ever i tried the expressions are in sheet3 Text object

Please find the file and help me out from this

Re: How to summarize the column for a table in set Analysis

sunny_talwar — Fri, 07 Sep 2018 11:26:43 GMT

What is the expected output you are looking to get?

Re: How to summarize the column for a table in set Analysis

bvssudhakar — Fri, 07 Sep 2018 11:36:58 GMT

It's around 20 to 24

If you have idea about power bi they used this formula

Openings till date =

CALCULATE (

CALCULATE(SUMX(SUMMARIZE(PlacementDate,PlacementDate[jobid] ),CALCULATE(MAX(PlacementDate[NoOpenings]))),

Here PlacementDate is Table Name

Re: How to summarize the column for a table in set Analysis

sunny_talwar — Fri, 07 Sep 2018 11:55:26 GMT

I don't, I have not used that before. I will see if I can get to anywhere between 20 - 24

Re: How to summarize the column for a table in set Analysis

bvssudhakar — Fri, 07 Sep 2018 12:09:55 GMT

Ok Thank you sunny

Re: How to summarize the column for a table in set Analysis

sunny_talwar — Fri, 07 Sep 2018 12:43:23 GMT

May be try this

=Sum(Aggr(Sum(DISTINCT {<Recruiter = {'Irfan Memon', 'Rajiv Ranjan', 'Aditi Saxena', 'Sneha Singh'}, DATETYPE = {'EnterDate'}, JobClassid = {4}>} NoOpenings), jobid))

Re: How to summarize the column for a table in set Analysis

bvssudhakar — Fri, 07 Sep 2018 13:44:30 GMT

Hi Sunny,

May be this was correct, If Select 2016 the output should be 0 and if select 2017 also the output should 0

But for me it is showing 2, and for those 2 years i have checked in table also for that enter id doesn't have 4 in job class id in 2016 year and 2017 year

And One more is In sheet 2 I have created tables for the filters not applying properly.

May be something i did wrong.

Can you check for me please.

Re: How to summarize the column for a table in set Analysis

bvssudhakar — Fri, 07 Sep 2018 13:52:00 GMT

For that tables If i select jobclassid = 7 it is showing only 2018 data

But in Text Objects how it is showing 2016 -2, 2017-2 I am not understanding

Please check for me once

Re: How to summarize the column for a table in set Analysis

sunny_talwar — Fri, 07 Sep 2018 14:01:16 GMT

I have no idea what you are looking to get. Can you put in simple word as to what you goal is instead of sharing an expression from POWER BI

Re: How to summarize the column for a table in set Analysis

bvssudhakar — Fri, 07 Sep 2018 14:22:40 GMT

Hi Sunny,

I want to create 5 Text objects and one hierarchy calendar as list box

1. I want to show Total Joiners for those 4 recruiters, So for that i made a relation ship with plc date column and i have taken distinct count of candidateID's

2. I want to Show Total Offers for those 4 recruiters, So for that i made a relation ship with offerdate column and i have taken distinct count of candidateID's

3. I want to Show Total Backouts for those 4 recruiters, So for that i made a relation ship with Declinedate column and i have taken distinct count of candidateID's

4. I want to Show Total jobs of job class id 4 for those 4 recruiters, So for that i made a relation ship with Enterdate column and i have taken distinct count of jobID's

5. I want to Show Current openings tilldate of job class id 4 for those 4 recruiters, So for that i am trying what to do i am not getting

Present i used for this expression for 5th which is given by you

=Sum(Aggr(Sum(DISTINCT {<Recruiter = {'Irfan Memon', 'Rajiv Ranjan', 'Aditi Saxena', 'Sneha Singh'}, DATETYPE = {'EnterDate'}, JobClassid = {4}>} NoOpenings), jobid))

Can you please check for all of them

Re: How to summarize the column for a table in set Analysis

sunny_talwar — Fri, 07 Sep 2018 14:39:58 GMT

Okay, I am still not 100% sure I understand, but going back to your selection of 2016 in the YEAR field... It seems that YEAR 2016 is associated with two EnterDate (25/04/2018 and 19/07/2018). Is this a data problem?