topic Re: Set Analysis and max date in QlikView

Set Analysis and max date

Anonymous — Wed, 09 Sep 2015 20:34:46 GMT

Hello, I'm trying to compare amounts based on the max month available.

My issue occurs whenever I select a month, the expressions sum the selected month's amount instead of the previous month's amount.

Here's an example of my code

First Column:

=sum({<Reporting_Period_Num = {'=(max(Reporting_Period_Num))'}>} Account_Balance)

Second column:

=sum({<Reporting_Period_Num = {'=(max(Reporting_Period_Num)-1)'}>} Account_Balance)

Re: Set Analysis and max date

sunny_talwar — Wed, 09 Sep 2015 20:40:11 GMT

You mean your second column shows you the sum for the selected period?

Re: Set Analysis and max date

Anonymous — Wed, 09 Sep 2015 20:41:42 GMT

Both columns do. When a month is not selected, it shows the sum for all periods as if the set analysis is being ignored

Re: Set Analysis and max date

sunny_talwar — Wed, 09 Sep 2015 20:43:06 GMT

See if this helps:

First Column:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num))"}>} Account_Balance)

Second column:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num)-1)"}>} Account_Balance)

Re: Set Analysis and max date

Anonymous — Wed, 09 Sep 2015 20:45:59 GMT

That just gives me 0's across the board

Re: Set Analysis and max date

sunny_talwar — Wed, 09 Sep 2015 20:47:28 GMT

I made some changes to the expression, did you use these?

First Column:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num))"}>} Account_Balance)

Second column:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num)-1)"}>} Account_Balance)

If not, try them. If you did, then can you share some date to check?

Re: Set Analysis and max date

Anonymous — Wed, 09 Sep 2015 21:09:11 GMT

Close. It works now until I select a month, then the column that takes the max period-1 turns to 0

Re: Set Analysis and max date

sunny_talwar — Wed, 09 Sep 2015 21:12:01 GMT

I think you will need to add other mastercalendar fields in here to work:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num)-1)"}, Month = , Year = , Date = >} Account_Balance)

or try this with a 1 to restrict any selections except the one (Reporting_Period_Num)

=Sum({1<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num)-1)"}>} Account_Balance)

Re: Set Analysis and max date

Anonymous — Wed, 09 Sep 2015 21:14:50 GMT

That works! The 1 in front is making the difference. Thank you so much!!

Re: Set Analysis and max date

sunny_talwar — Wed, 09 Sep 2015 21:16:40 GMT

But make sure you understand what it is doing. 1 in the front will make the expression not respond to any selection except Reporting_Period_Num. Is that what you want? If not, I would suggest using the other expression with all the master calendar fields in it.

Best,

Sunny

Re: Set Analysis and max date

Anonymous — Wed, 09 Sep 2015 21:30:53 GMT

You're right. Utilizing the master calendar is the better way to go here as I don't want to ignore the other filters

Re: Set Analysis and max date

Fri, 10 Feb 2017 05:55:12 GMT

Hi Sunny,

Thanks for providing this response this has helped my implement a comparison between current and previous period.

Count({$<Period_seq={$(=max(Period_seq))}>} NPS)

Count({$<Period_seq={$(=max(Period_seq-1))}>} NPS)

1=30/11/2015

2=31/12/2015

3=31/1/2016

etc

I now want to include all data over the last 12 months, so need to add a greater than sign to the above, something like:

Count({$<Period_seq>{$(=max(Period_seq-13))}>} NPS)

Can you help?

Best wishes

Re: Set Analysis and max date

Anil_Babu_Samineni — Fri, 10 Feb 2017 06:04:20 GMT

May be this?

If(Period_seq > AddMonths(Max(Period_seq),-12), Count(NPS))

Count({<Period_seq = {"> $(=AddMonths(Max(Period_seq),-12))"}>}NPS)

Count({<Period_seq = {"> $(=AddYears(Max(Period_seq),-1))"}>}NPS)

Count ({<Period_seq = {"=Aggr(If(Period_seq >AddMonths(Max(Period_seq),-1),Period_seq ),Period_seq )"}>} NPS)

Re: Set Analysis and max date

sunny_talwar — Fri, 10 Feb 2017 06:14:33 GMT

Try like this:

Count({$<Period_seq = {"$(='>=' & Max(Period_seq-13) & '<=' & Max(Period_seq))"}>} NPS)

I would add less then equal to Max(Period_seq) to make sure you still see 12 months of data if you have made selections. Otherwise if you don't but the <= condition, you will see a lot of more data then you expect after selection in Period_seq

Re: Set Analysis and max date

Fri, 10 Feb 2017 06:41:21 GMT

Thanks so much! This works!!