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: 
nishanthi_8
Creator
Creator

Set Analysis in Qlik Sense

I want to get the latest month detail to be displayed in the bar chart. I gave the following expression:

count(distinct {<Months={$(=Max(Months)}>}"User Name")

But its not executing properly. It is just giving the value 0. Though the expression works well in pivot table. That is no problem in case of 'Months' field name.

9 Replies
Chanty4u
MVP
MVP

try this witout quotes

count(distinct {<Months={$(=Max(Months)}>}[User Name])

nishanthi_8
Creator
Creator
Author

no if I use that I am getting '-' this one in KPI and nothing in bar chart

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

is your Months data is like 2014-01 or 2014-01-31 or something in Date format?

then use this expression. You have to use single quotes.

count(distinct {<Months={'$(=Max(Months)'}>}[User Name])   

//This will respect you selection on Months/Year/etc.. fields.


If you want to ignore those then use this..


count(distinct {<Year=,Month=,Months={'$(=Max(Months)'}>}[User Name])   

//Assuming you have Year & Month Fields in data model. Change it according to your model.

nishanthi_8
Creator
Creator
Author

Hi what my Months field contain is  Month(MakeDate(2015,8)) as Months

So what has to be done? Anyhow it has Months in the proper format hope so it is in correct format.

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

so that function will give you a string output like "Jan, Feb, Mar,etc..."

so you can not use MAX --- for strings it will be MAXSTRING....

but this won't solve the problem as i think you are trying to get current month value. Maxstring will go by alphabetical order and it doesnt know JAN is 1st and DEC is last.

so if you are trying to get current month data in your chart try adding a new column in data..

Date(MakeDate(2015,8),'YYYYMM') as Months

and use the above said expression

count(distinct {<Months={'$(=Max(Months)'}>}[User Name])

nishanthi_8
Creator
Creator
Author

do you mean instead of  Month(MakeDate(2015,7)) as Months, it is to be replaced as Date(MakeDate(2015,8),'YYYYMM') as Months ?

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

Yes...

usually i should suggest you should have a MasterCalendar in your model or some sort to help this kind of stuff..

if you are using Months field in your dashboard then try adding this as a new column...

Date(MakeDate(2015,8),'YYYYMM') as YearMonth


count(distinct {<YearMonth={'$(=Max(YearMonth)'}>}[User Name])

nishanthi_8
Creator
Creator
Author

Really thanks a lot it worked

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

Great.

If it is really helps please mark the question as HELPFUL or CORRECT so that other team members can find this useful.

Thanks,

Phani