Date - set Analysis

[#1]

Hi,

I'm a beginner to Qlikview.

I'm trying to write an expression for a graphic to sum these fields [retr.mese/importo], [retr.mese/contributi], [retr.mese/inail], [lav.strao/importo],[lav.strao/contributi], [lav.strao/inail] from 06/2012 to 09/2012.

Below an example:

codice   azienda	codice filiale	matricola   dipendente	anno cedolini	mese cedolini	retr.mese/quantita'	retr.mese/importo	retr.mese/contributi	retr.mese/inail	lav.strao/quantita'	lav.strao/importo	lav.strao/contributi	lav.strao/inail
4	1	199	2012	1	78	857,94	255,11	15,6	5,5	5,35	1,66	0,1
4	1	199	2012	2	39,5	532,47	154,1	9,68	6,8	6,62	2,05	0,12
4	1	199	2012	3	83	839,75	250,07	17,82	10	9,82	3,04	0,21
4	1	199	2012	4	81,5	893,39	265,59	18,94	11,5	11,29	3,5	0,24
4	1	199	2012	5	71,5	942,88	281,88	20,01	16	15,71	4,87	0,33
4	1	199	2012	6	85	864,31	245,35	18,33	10,5	64,33	19,93	1,36
4	1	199	2012	7	49	715,88	208,95	15,17	5	4,91	1,52	0,1
4	1	199	2012	8	90	977,26	292,63	20,72	10,5	10,31	3,19	0,22
4	1	199	2012	9	89	892,04	266	18,91	10	9,97	3,09	0,21
4	1	199	2012	10	113,5	1149,58	345,58	24,39	10	9,97	3,09	0,21
4	1	199	2012	11	105	1087,74	326,61	23,08	0	0	0	0
4	1	199	2012	12	63,6	1071,45	307,95	22,73	35,5	188,88	58,52	4,01

And then how can I calculate the difference between the sum of the fields [retr.mese/importo], [retr.mese/contributi], [retr.mese/inail], [lav.strao/importo],[lav.strao/contributi], [lav.strao/inail] from 06/2012 to 09/2012 and from 06/2013 to 09/2013?

Any suggestions greatly appreciated.

Regards,

Andrea

[#2]

Hi! I’msorry but I have another problem. The expression to sum fields from 11/2012 to
03/2013 works. When I try to create a new graphic, I don’t know how to calculate
new dimensions  as 11/2011 – 03/2012 and 11/2012– 03/2013. Maybe can you help me another time pls?

[iktrayanov]

If the expression for

11/2012 to 03/2013 is

  Sum({$<[anno cedolini] = {2012},[mese cedolini]= {'11','12'}>}[retr.mese/importo] + [retr.mese/importo] +[retr.mese/contributi] + [retr.mese/inail] + [lav.strao/importo] +[lav.strao/contributi] + [lav.strao/inail]) +

Sum({$<[anno cedolini] = {2013},[mese cedolini]= {'1','2','3'}>}[retr.mese/importo] + [retr.mese/importo] +[retr.mese/contributi] + [retr.mese/inail] + [lav.strao/importo] +[lav.strao/contributi] + [lav.strao/inail])

then for 11/2011 – 03/2012 you just replace the years like this

Sum({$<[anno cedolini] = {2011},[mese cedolini]= {'11','12'}>}[retr.mese/importo] + [retr.mese/importo] +[retr.mese/contributi] + [retr.mese/inail] + [lav.strao/importo] +[lav.strao/contributi] + [lav.strao/inail]) +

Sum({$<[anno cedolini] = {2012},[mese cedolini]= {'1','2','3'}>}[retr.mese/importo] + [retr.mese/importo] +[retr.mese/contributi] + [retr.mese/inail] + [lav.strao/importo] +[lav.strao/contributi] + [lav.strao/inail])

[#4]

Ok. I edit the expression for these period: 2011-2012, 2012-2013, ecc.

My problem is when I create my graphic I dont know how edit the properties in Chart Properties/Dimension.

I can't select fields Months or Year because I want that my graphic reports these specific range of fields: 11/2012 - 03/2013, ecc.

I think I need to create a new dimension, as 2011/2012, that show me total cost from 11/2011 to 03/2012 on a label.

You can find attached an example of what I'm trying to do.

Thank you so much for your patience! I really appreciate!

[iktrayanov]

Take a look at this(See attached I used your sample data).

I created a calculated dimension called 'Period' and then your expression is a simple sum.

Let me know if that works.

[#6]

Thank you so much for your time!

[#7]

Hi! Could you give me please any more instructions?

I’m trying to create a graphic with the following expressions:

1. Sum of business retributions of the business selected in a input box (Select Business);
2. Average of business retributions of the benchmark selected in a list box (Select Benchmark).

        If user chooses:

• Option 1 – I consider only business number 4 and 95;
• Option2 – I consider only business number 112 and 95;
• Option 3 – I consider retribution of business number 112 and 4;

      This a very simple example, but I need to create a benchmark with a lot of lists of business and connect the user choice to my      average graphic expression. I think to insert these benchmarks in a list box.

     If I want to calculate the average of retribution fields of all my business records,  is it correct the use of this simple
     expression:  =avg(retribuzione+contributi+straordinario+inail)

Moreover,I’d like to create a label to show the business selected and the name of benchmark chosen for the average.

I try to use these variable:  “vBusiness “ forbusiness selected and  “vBenchmark” for benchmark option selected.

You can find an example attached.

Do you think it’s possible to do?

I really appreciate any help you can provide.