topic Re: AGGR () and comparative set analysis in QlikView

AGGR () and comparative set analysis

sibrulotte — Fri, 21 Mar 2014 13:41:21 GMT

Hey, so I tried this and it's pretty accurate for the most part.

This is my current:

sum (aggr(round(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Numéro du type d'écriture] -= {'FERM'} >}([Montant cumulé aad])* %CR004M)),[Niveau 2 ERD],[Niveau 3 ERD],[Niveau 4 ERD],[Description de l'extra]))

All the values are on point.

This is my comparative: (same set analysis, I just add the comp1 period)

sum(aggr(round(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Année-période financière]= [Année-période financière comp 1], [Numéro du type d'écriture] -= {'FERM'} >}([Montant cumulé aad])* %CR004M)),[Niveau 2 ERD],[Niveau 3 ERD],[Niveau 4 ERD],[Description de l'extra]))

Unfortunatly this one skips over some aggregations of the [Description de l'extra] that are no longer existing in the "current" period.

IE, I'm missing values for extra "XYZ" that exist in the comp 1 period, but not in current period.

What do I do to work around this?

Re: AGGR () and comparative set analysis

Nicole-Smith — Fri, 21 Mar 2014 13:52:06 GMT

Try like this:

sum(aggr(round(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Année-période financière]={'=[Année-période financière comp 1]'}, [Numéro du type d'écriture] -= {'FERM'} >}([Montant cumulé aad])* %CR004M)),[Niveau 2 ERD],[Niveau 3 ERD],[Niveau 4 ERD],[Description de l'extra]))

Or like this:

sum(aggr(round(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Année-période financière]={'=[Année-période financière]=[Année-période financière comp 1]'}, [Numéro du type d'écriture] -= {'FERM'} >}([Montant cumulé aad])* %CR004M)),[Niveau 2 ERD],[Niveau 3 ERD],[Niveau 4 ERD],[Description de l'extra]))

Re: AGGR () and comparative set analysis

sibrulotte — Fri, 21 Mar 2014 14:08:25 GMT

Are you sure about that synthax?

That gave a bunch of zeros.
I added the usual $( to evaluate and get this

sum

(aggr(round(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Année-période financière]={'$(=[Année-période financière comp 1])'}, [Numéro du type d'écriture] -= {'FERM'} >}([Montant cumulé aad])* %CR004M)),[Niveau 2 ERD],[Niveau 3 ERD],[Niveau 4 ERD],[Description de l'extra]))

Same results as mine, missing values.

For this attempt:

Thanks for the try

Re: AGGR () and comparative set analysis

Nicole-Smith — Fri, 21 Mar 2014 14:10:04 GMT

One more try...

sum(aggr(round(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Année-période financière]=P([Année-période financière comp 1]), [Numéro du type d'écriture] -= {'FERM'} >}([Montant cumulé aad])* %CR004M)),[Niveau 2 ERD],[Niveau 3 ERD],[Niveau 4 ERD],[Description de l'extra]))

Re: AGGR () and comparative set analysis

sibrulotte — Fri, 21 Mar 2014 14:31:35 GMT

I didn't think about trying the "possible".

But I'm still missing the values