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: 
Anonymous
Not applicable

Dates in set analysis

Hi

I have a set analysis that looks at the min & max date (as below)

(Sum({<

[Turnover.Start_Month]={"$(=Date((Min(Turnover.Start_Month)), 'DD/MM/YYYY'))"}

>} [Turnover.First_Day_of_Month_Count]))

This works fine without the added dimension of fiscal year, However I now need to bring in the Fiscal year as a dimension in my pivot table, but my table shows as below for the start and end headcount.

     

Dates.FiscalYear     Start      Headcount     End      Headcount          Overall     Leavers
15/1666080844
16/1707067707

The set analysis uses the whole range to work out the min & max, but I need to show a start and end headcount for each year.  Any ideas?

Thanks

Phil

1 Solution

Accepted Solutions
tresesco
MVP
MVP

What if you use DISTINCT like:

Start : FirstSortedValue( Distinct Aggr(Sum(Value),Year, Month),Month)

End: FirstSortedValue( Distinct Aggr(Sum(Value),Year, Month),-Month)


?

View solution in original post

12 Replies
Anil_Babu_Samineni

Would you share sample

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sarvesh
Creator III
Creator III

write your code like this:

TEMP:

LOAD

num(min(EmployeeDirectory.DOH)) AS MinDate,

num(max(EmployeeDirectory.DOH)) AS MaxDate


RESIDENT

Employees

;

;

LET vMinDate = peek('MinDate', 0, 'TEMP');;

LET vMaxDate = peek('MaxDate', 0, 'TEMP');;

SET vRangeStart = =vMinDate;

SET vRangeEnd = =vMaxDate;

Anonymous
Not applicable
Author

I don't think that will work as it needs to identify the start and end for each year, not the range.

Anonymous
Not applicable
Author

It's like I need to create a variable for each start end and end for each year and then create the set analysis on that, but not sure where to begin.

Possibly ?

sarvesh
Creator III
Creator III

try once and show whatever result will come.

Anonymous
Not applicable
Author

I have attached a sample to the original post.

Any help welcome.

Cheers

Phil

tresesco
MVP
MVP

Use FirstSortedValue() like:

Start : FirstSortedValue(Aggr(Sum(Value),Year, Month),Month)

End: FirstSortedValue(Aggr(Sum(Value),Year, Month),-Month)

Set analysis doesn't evaluate row wise, rather once for a chart.

PFA

Kushal_Chawda

try this

Sum( total <Dates.FiscalYear> {<[Turnover.Start_Month]={"$(=Date((Min(Turnover.Start_Month)), 'DD/MM/YYYY'))"}>}[Turnover.First_Day_of_Month_Count]))

Anonymous
Not applicable
Author

Hi

Definitely works if there is only one line of data, but there are multiple lines of data for each month as the data is based on headcount by division. e.g.  April = Division1 has 1000 & Division2 has 1000 = Total April 2000.


I should have put that on my original message. 

Unfortunately your resolution does not work with this.

I have revised the data with your version and attached again so you can see.

So close ......

Thoughts?

Phil