What's wrong with set analysis ?

[#1]

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 ^^

[#2]

Someone has an idea ?

[sasikanth]

Hi

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])

[#4]

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.

[#5]

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

[#6]

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

[#7]

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.

[matthewjbryant · Accepted solution]

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)"}

[#9]

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')

                    )

               )

          )

     )

)

[#10]

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.