3 Replies Latest reply: Mar 16, 2015 6:45 AM by Henric Cronström

# Count et sum sur un perimètre commun

Bonjour,

J'ai une table avec 3 colonnes : un id de produit, une date, et un prix. La table recense tous les prix par couple id/date.

Je souhaiterai effectuer une comparaison du prix moyen des produits sur 2 plages de date, mais sur perimètre commun à ces 2 plages de dates.

Dans l'exemple joint, j'ai essayé en utilisant la fonction P(), mais ca ne fonctionne pas. Je souhaiterai donc avoir, dans le graphique 'count', la même valeur pour les 2 dates soit 11 ids.

Deja, pensez vous que cela soit possible ? Je suis preneur de toute idée.

Merci d'avance

Steph

• ###### Re: Count et sum sur un perimètre commun

You need to change your Set Analysis from

Jour={"=02/03/2015"}

to

Jour={'02/03/2015'}

Further, I would approach the problem slightly differently. I would populate the source data so that you have prices for all days, see How to populate a sparsely populated field

How to do this when you have two keys (Id and Jour) is described in Generating Missing Data In QlikView.

Good Luck

HIC

• ###### Re: Count et sum sur un perimètre commun

Hi Henric,

Tks for your solution, it works.

I can't generate missing data because if a data is missing, it is because the product does not exist. Products can appear and disappear each day. That's why i want to take into the average only common products on two dates ranges.

Your solution works on one date. What about on a range of dates ? Imagine i want to set into a global variable the comparison date. I want to compute and compare the sum/count on one week before and one week after, always on common products. How can i do this ?

• ###### Re: Count et sum sur un perimètre commun

Do you really need to code this? Wouldn't it be a solution that the user just makes a selection in the Jour field?

If you want to code it, you could use two variables displayed in an Input box: MinDate and MaxDate. These could then be used in a Set Analysis expression:

Jour={">=\$(MinDate)<=\$(MaxDate)"}

HIC