Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bvssudhakar
Creator III
Creator III

How to summarize the column for a table in set Analysis

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.smupriyalvp24manoj217loveisfailrwunderlichrajivmeher

Thank you in advance

1 Solution

Accepted Solutions
sunny_talwar

May be try this

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

View solution in original post

17 Replies
sunny_talwar

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

bvssudhakar
Creator III
Creator III
Author

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

sunny_talwar

What is the expected output you are looking to get?

bvssudhakar
Creator III
Creator III
Author

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

sunny_talwar

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

bvssudhakar
Creator III
Creator III
Author

Ok Thank you sunny

sunny_talwar

May be try this

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

bvssudhakar
Creator III
Creator III
Author

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.

bvssudhakar
Creator III
Creator III
Author

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