Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

What's wrong with set analysis ?

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

1 Solution

Accepted Solutions
matthewjbryant
Creator II
Creator II

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

View solution in original post

10 Replies
Not applicable
Author

Someone has an idea ?

sasikanth
Master
Master

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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
Creator II
Creator II

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

Not applicable
Author

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

                    )

               )

          )

     )

)

Not applicable
Author

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.