Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nishanthi_8
Contributor

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
sureshqv
Esteemed Contributor III

Re: Set Analysis in Qlik Sense

try this witout quotes

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

nishanthi_8
Contributor

Re: Set Analysis in Qlik Sense

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

phaneendra_kunc
Valued Contributor III

Re: Set Analysis in Qlik Sense

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
Contributor

Re: Set Analysis in Qlik Sense

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
Valued Contributor III

Re: Set Analysis in Qlik Sense

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
Contributor

Re: Set Analysis in Qlik Sense

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
Valued Contributor III

Re: Set Analysis in Qlik Sense

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
Contributor

Re: Set Analysis in Qlik Sense

Really thanks a lot it worked

phaneendra_kunc
Valued Contributor III

Re: Set Analysis in Qlik Sense

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

Community Browser