Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

giovanneb
Contributor II

Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Hello I have the following situation:

used the following set Analysis:

sum ({$} <Month_Sale = {$(=month(addmonths(max(Date_Sale), -1)))}> Qty)

By using the expression above it works correctly when the selection year is exergar filter to the data of the previous month, my problem happens when I select the month of January because this expression brings zeroed data, how can I resolve this situation? thank you

Tags (1)
1 Solution

Accepted Solutions

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

If I understand what you want correctly then that's not possible with set analysis. The sets are calculated per chart not per row. First the set is calculated and then the dimensions and expressions are applied to the set. You'll have to use something like AsOf tables. See attached example.


talk is cheap, supply exceeds demand
10 Replies

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Since you've selected a year you need to exclude selections in the year field in the expression. Otherwise you can't get the Qty from the last month of the previous year, only from the selected year. Assuming your year field is named Year try this:

sum ({$} <Month_Sale = {$(=month(addmonths(max(Date_Sale), -1))), Year=}> Qty)


talk is cheap, supply exceeds demand
giovanneb
Contributor II

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

I added the year now when I select the values ​​month are accrued and should not

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Right you are. Try this then:

sum ({$} <Date_Sale={'=>$(=monthstart(addmonths(max(Date_Sale), -1)))<$(=monthstart(max(Date_Sale)))'},MonthYear=, Year=}> Qty)

You could make things a lot easier for yourself if you start using the qvc library. See here for a tutorial.


talk is cheap, supply exceeds demand
giovanneb
Contributor II

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

I tried using the expression presented by you got an error of expression, what could it be? thank you

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

My mistake. A typo. Date_Sale={'=> should be Date_Sale={'>=


talk is cheap, supply exceeds demand
shaik_basha
Contributor III

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Hi,

Can you please provide test application,it will be works properly no problem.

Regards

giovanneb
Contributor II

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

Hello still can not'll post a sample document QV, I believe I am missing something in the expression

Follow Document

Follow the document would need to analyze that:

                       Qty Initial   Qty Last

Dec 2012          496           0

Jan 2012          1457          496

Feb 2012          1450         1457

giovanneb
Contributor II

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

I'll post a exempl document, thanks

Re: Set Analysis to present data from the previous month when the filtered dimension this year as the current selection

If I understand what you want correctly then that's not possible with set analysis. The sets are calculated per chart not per row. First the set is calculated and then the dimensions and expressions are applied to the set. You'll have to use something like AsOf tables. See attached example.


talk is cheap, supply exceeds demand
Community Browser