12 Replies Latest reply: Apr 25, 2013 4:13 AM by Gerald Tagudando

# Calculate 1 year when only 1 month is selected

Hi Guys,

Please let me explain my problem brielfly:

This is my data for 2 years:

2011:

 Group Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec A 100 110 120 130 140 150 160 170 180 190 200 210 B 150 160 170 180 190 200 210 220 230 240 250 260 C 200 210 220 230 240 250 260 270 280 290 300 310

2012:

 Group Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec A 80 90 100 110 120 130 140 150 160 170 180 190 B 130 140 150 160 170 180 190 200 210 220 230 240 C 180 190 200 210 220 230 240 250 260 270 280 290

Formula:

Sales Ratio = Sum Sales this month divided by Sum Sales of the whole year

(ex. If user selects Aug-2012,then Sum (Sales for Aug 2012) divided by Sum (Sales from Aug 2011 to Aug 2012).

Catch:

User wants only to select Aug 2012 in the listbox. Now the user wants to see the result of Sales Ratio. The answer should be: 0.080213903

Question:

What will my expression look like?

Gerald

• ###### Re: Calculate 1 year when only 1 month is selected

Hi Son of Sardar,

I tried using set analysis but it wont fetch the months that are not selected by the user . Thanks by the way.

• ###### Re: Calculate 1 year when only 1 month is selected

Hi Gerald ,

Its ok! why dont you just give a sample file to me with dummy data.

• ###### Re: Calculate 1 year when only 1 month is selected

Hi Son of Sardar,

I know its funny but I can't attach my qvw file. How do I do it? sorry about this. I just started in QlikView discussions a few days ago.

Thanks,

Gerald

• ###### Re: Calculate 1 year when only 1 month is selected

hi,

it is right top corner when you write any post

• ###### Re: Calculate 1 year when only 1 month is selected

Hi Son of Sardar,

I think I found it! Please see attached file.

Thanks,

Gerald

• ###### Re: Calculate 1 year when only 1 month is selected

There is no file attached... please check

• ###### Re: Calculate 1 year when only 1 month is selected

Hi Sushil,

The attachments is located in the original pos. Thanks in advance!

Regards,

Gerald

• ###### Re: Calculate 1 year when only 1 month is selected

you can create two variables

1. first variable will contain the selected date

2. second variable will contain the date of last year

then compare these two variable in your set analysis.

It is better if you post a sample app...

HTH

Sushil

• ###### Re: Calculate 1 year when only 1 month is selected

Hi Sushil,

Please see attached file. Thanks for the help!

Thanks,

Gerald

• ###### Re: Calculate 1 year when only 1 month is selected

See if the attached file helps.

Thanks Amar

• ###### Re: Calculate 1 year when only 1 month is selected

Hello amars,

"See IF the attached file helps".... IT REALLY HELPED!!! Talk about humility.

Thank you very much. The formula that you've used is correct.

Thank you my friend,

Gerald