topic Re: Date - set Analysis in QlikView

Date - set Analysis

Sun, 22 Dec 2013 18:52:33 GMT

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

Re: Date - set Analysis

Sun, 22 Dec 2013 19:22:08 GMT

Test

Re: Date - set Analysis

Anonymous — Sun, 22 Dec 2013 19:27:35 GMT

http://community.qlik.com/thread/33737

Re: Date - set Analysis

Mon, 23 Dec 2013 19:45:16 GMT

Can someone help me pls?

Re: Date - set Analysis

iktrayanov — Mon, 23 Dec 2013 20:07:53 GMT

I am not really sure if I understand your question but too get the sum of [retr.mese/importo] for example

you can use something like

Sum({$<[anno cedolini] = {2012},mese cedolini= {'6','7','8','9'}>}[retr.mese/importo])

Re: Re: Date - set Analysis

iktrayanov — Mon, 23 Dec 2013 20:18:38 GMT

Attached is an example

Re: Date - set Analysis

Mon, 23 Dec 2013 22:20:01 GMT

thank you so much for your help!

Re: Re: Date - set Analysis

Fri, 27 Dec 2013 17:23:25 GMT

Hi! If I want to sum fields from 11/2012 to 03/2013, I can use something like:

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

Thanking you in advance for your help in this matter, I look forward to your reply!

Re: Re: Date - set Analysis

iktrayanov — Mon, 30 Dec 2013 15:26:09 GMT

This should work.Only one detail I noticed the brackets are missing around [mese cedolini].

Re: Re: Date - set Analysis

Mon, 30 Dec 2013 20:51:09 GMT

Thank you!

Re: Re: Date - set Analysis

Wed, 01 Jan 2014 21:50:36 GMT

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?

Re: Date - set Analysis

iktrayanov — Wed, 01 Jan 2014 22:37:59 GMT

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

Re: Date - set Analysis

Thu, 02 Jan 2014 00:01:10 GMT

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!

Re: Re: Date - set Analysis

iktrayanov — Thu, 02 Jan 2014 01:11:02 GMT

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.

Re: Re: Date - set Analysis

Thu, 02 Jan 2014 21:06:14 GMT

Thank you so much for your time!

Re: Re: Date - set Analysis

Sun, 05 Jan 2014 16:39:37 GMT

Hi! Could you give me please any more instructions?

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

Sum of business retributions of the business selected in a input box (Select Business);
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.