topic Re: What's wrong with set analysis ? in QlikView

What's wrong with set analysis ?

Fri, 04 Jul 2014 11:57:48 GMT

Hi,

i'm trying to create 2 set analysis for my project.

1* Must give the cost for an date (year -1) of the actual selection(year)

2* Must give the cost for an date (month+1) of the actual selection(month)

Actually, the user can choose in 2 dimensions ([Année de Quittancement],& [Moisde Quittancement]), the cost of the contract. Imagine that the user choose : july(7)- 2013, he as the cost for this date. My set analysis must give him (on the same line) the information for August(8)-2013 & december 2012.

To understand it :

TABLE1:

QTTERM as [Date de Quittancement],

Year(QTTERM) as [Année de Quittancement],
Month(QTTERM) as [Mois de Quittancement],
Num#(Year(QTTERM)-1)as [Année Date Solde-1],
Num#(Month(QTTERM)+1)as [Mois Date Solde+1],

;

TABLE 2:

...

QTTERM as [Date Solde],
ETAT_DU_SOLDE   as [Montant Solde],
Year(QTTERM)   as [Année Date Solde],
Month(QTTERM)   as [Mois Date Solde];

Actually, i've try this :

(1*) =sum( {$<[Année Date Solde]={[Année Date Solde]}, [Mois Date Solde]={[Mois Date Solde]={[Mois Date Solde+1]}>}[Montant Solde] ) (KO)

(2*) =sum( {$<[Année Date Solde]={[Année Date Solde-1]}, [Mois Date Solde]={déc.}>}[Montant Solde] ) (KO)

(2*) =sum( {$<[Année Date Solde]={2012}, [Mois Date Solde]={déc.}>}[Montant Solde] ) (OK) but I want a dynamique résultat)

What's wrong ?

Thx in advice & sorry for my bad English ^^

Re: What's wrong with set analysis ?

Mon, 07 Jul 2014 07:43:15 GMT

Someone has an idea ?

Re: What's wrong with set analysis ?

sasikanth — Mon, 07 Jul 2014 07:55:51 GMT

Before proceed please make sure that QTTERM is in date Format,

then try some thing like this

2nd

=sum({$<[Année Date Solde]={'$(=Num#(Year(QTTERM)-1))'},[Mois Date Solde]={'$(=Num#(Month(QTTERM)+1))'}>}[Montant Solde])

Re: What's wrong with set analysis ?

Mon, 07 Jul 2014 08:18:13 GMT

In my request SQL, I use a TO_DATE) :

TO_DATE(KBORENC.QTTERM,'YYYY/MM') AS QTTERM

I 've try your set analysis and the result is 0 even if I choose different date.

Re: What's wrong with set analysis ?

Mon, 07 Jul 2014 09:12:17 GMT

First of all when you have a string you should enclose it with ' '

Re: What's wrong with set analysis ?

Mon, 07 Jul 2014 09:13:17 GMT

=sum( {$<[Année Date Solde]={2012}, [Mois Date Solde]={'déc'}>}[Montant Solde] )

Re: What's wrong with set analysis ?

Mon, 07 Jul 2014 10:07:18 GMT

Hi TJETJETJE,

Thx for the answer but I'ld like a dynamic set analysis.

Because the result [Montant Solde], change if the user select Month or Year.

Re: What's wrong with set analysis ?

matthewjbryant — Mon, 07 Jul 2014 10:18:49 GMT

If you want the results to change depending on the month or year selected you don't have to use set analysis. Just put a list box of Month, and a list box of Year on the page.

The way to make information dynamic using set analysis is to use variables, but from what you're saying I don't think that's the best way forward. Syntax is:

[Année Date Solde] = {"<=$(vYEAR)"}

Re: What's wrong with set analysis ?

Mon, 07 Jul 2014 10:27:34 GMT

I've find a creepy solution ...

IF([Année de Quittancement]='2014',sum({$<[Année Date Solde]={'2013'}, [Mois Date Solde]={'dec'}>}[Montant Solde]),
      IF([Année de Quittancement]='2013',sum({$<[Année Date Solde]={'2012'}, [Mois Date Solde]={'dec'}>}[Montant Solde]),
           IF([Année de Quittancement]='2012',sum({$<[Année Date Solde]={'2011'}, [Mois Date Solde]={'dec'}>}[Montant Solde]),
                IF([Année de Quittancement]='2011',sum({$<[Année Date Solde]={'2010'}, [Mois Date Solde]={'dec'}>}[Montant Solde]),
                     IF([Année de Quittancement]='2010',sum({$<[Année Date Solde]={'2009'}, [Mois Date Solde]={'dec'}>}[Montant Solde]),
                          IF([Année de Quittancement]='2009',sum({$<[Année Date Solde]={'2008'}, [Mois Date Solde]={'dec'}>}[Montant Solde]),'KO')

)

Re: What's wrong with set analysis ?

Mon, 07 Jul 2014 10:37:04 GMT

Hi matthewjbryant,

If you want the results to change depending on the month or year selected you don't have to use set analysis. Just put a list box of Month, and a list box of Year on the page.

Your right, I think i'll do that if i didn't find a good solution.

Re: What's wrong with set analysis ?

matthewjbryant — Mon, 07 Jul 2014 10:53:21 GMT

Hi Aritas,

For (1) set up a variable called vPreviousYear and use this code:

=GetFieldSelections(Year)-1

Then in the set analysis use the criteria [Année Date Solde] = {$(vPreviousYear)}

A similar thing can be done for (2)