topic Re: Dates in set analysis in QlikView

Dates in set analysis

Anonymous — Tue, 21 Mar 2017 09:01:30 GMT

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/16	6608	0	844
16/17	0	7067	707

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

Re: Dates in set analysis

Anil_Babu_Samineni — Tue, 21 Mar 2017 09:12:18 GMT

Would you share sample

Re: Dates in set analysis

sarvesh — Tue, 21 Mar 2017 09:17:42 GMT

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;

Re: Dates in set analysis

Anonymous — Tue, 21 Mar 2017 09:23:29 GMT

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

Re: Dates in set analysis

Anonymous — Tue, 21 Mar 2017 09:25:29 GMT

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 ?

Re: Dates in set analysis

sarvesh — Tue, 21 Mar 2017 09:28:02 GMT

try once and show whatever result will come.

Re: Dates in set analysis

Anonymous — Wed, 29 Mar 2017 09:38:00 GMT

I have attached a sample to the original post.

Any help welcome.

Cheers

Phil

Re: Dates in set analysis

tresB — Wed, 29 Mar 2017 09:49:03 GMT

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

Re: Dates in set analysis

Kushal_Chawda — Wed, 29 Mar 2017 09:50:34 GMT

try this

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

Re: Dates in set analysis

Anonymous — Wed, 29 Mar 2017 10:29:54 GMT

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

Re: Dates in set analysis

tresB — Wed, 29 Mar 2017 11:21:03 GMT

What if you use DISTINCT like:

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

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

Re: Dates in set analysis

Anonymous — Wed, 29 Mar 2017 11:28:23 GMT

Surely there must be a way to overcome this. Pulling my hair out here.

Anyone any ideas?

attached sample again in case you need it.

Cheers

Phil

Re: Dates in set analysis

Anonymous — Wed, 29 Mar 2017 12:08:19 GMT

Fantastic !!!!!!!!!

It works a treat.

Unfortunately as it is correct, it has identified a slightly different problem in one of the other expressions (unrelated), but I am going to post this separately to see if I can get resolution.

Tresesco, thank you so much !!

Phil