Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a set expression which returns the number of employees fulfilling the below criteria
=Count (Distinct {$<Rundate={$(vRundate)}, Maxdate={'<$(vMaxdate)'} >} EmpID)
where vRundate is 201401 and vMaxdate is 201408
Now my query is that i want to put this in a chart where my dimension will be Month and vRundate and vMaxdate will change based on Month.
Right now its a variable and can store only one value at a time but since my chart will have Month as dimension and it will have values from Jan-Aug so my variable value should change for every month as highlighted in the table below.
Month | vRundate | vMaxdate |
Jan | 201401 | 201402 |
Feb | 201402 | 201403 |
Mar | 201403 | 201404 |
Apr | 201404 | 201405 |
May | 201405 | 201406 |
Jun | 201406 | 201407 |
Jul | 201407 | 201408 |
How can i do this ? Can we write expression inside a set expression and pass values of Rundate and Maxdate like Rundate= Max(Month)+1 or simething like this ?
Thanks a lot in advance,
Sumit
Yes you can write an expression inside set like below
=Count (Distinct {$<Rundate={$(= Max(Month)+1)}, Maxdate={'<$(vMaxdate)'} >} EmpID)
Doesn't work. It doesn't take Max as a function
Missed bracket in the expression
=Count (Distinct {$<Rundate={$(= Max(Month)+1))}, Maxdate={'<$(vMaxdate)'} >} EmpID)
Bracket was already there. However giving extra bracket would give an error.
hi
in this case you can't use set analysis
as set analysis is like making a selection in the model , it will return the same results in all month
you can try something like assuming you have a date field
Count (if(Rundate=num(year(monthstart(Date))&num(month(monthstart(Date)))) and
Maxdate <num(year(monthstart(Date))&num(month(monthstart(Date)))+1), EmpID)
You need to specify your variable names with generalised month variable without using the '=' sign in the expression for the variablr i.e. vRundate and vMaxdate.
Hope this helps
You are right. Can you post sample qvw.
Was that reply for me?
No Gaurav