Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

gerald_t
New Contributor

Calculate 1 year when only 1 month is selected

Hi Guys,

I would like to ask for your help regarding my problem.

Please let me explain my problem brielfly:

This is my data for 2 years:

2011:

GroupJanFebMarAprMayJunJulAugSepOctNovDec
A100110120130140150160170180190200210
B150160170180190200210220230240250260
C200210220230240250260270280290300310

2012:

GroupJanFebMarAprMayJunJulAugSepOctNovDec
A8090100110120130140150160170180190
B130140150160170180190200210220230240
C180190200210220230240250260270280290

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?

Thanks in advance and godbless!

Gerald

Tags (2)
1 Solution

Accepted Solutions
amars
Valued Contributor

Re: Calculate 1 year when only 1 month is selected

See if the attached file helps.

Thanks Amar

12 Replies
sujeetsingh
Honored Contributor III

Re: Calculate 1 year when only 1 month is selected

sushil353
Honored Contributor II

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

gerald_t
New Contributor

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.

sujeetsingh
Honored Contributor III

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.

gerald_t
New Contributor

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

sushil353
Honored Contributor II

Re: Calculate 1 year when only 1 month is selected

hi,

use advanced editor option and there you will get option to upload your sample file

it is right top corner when you write any post

gerald_t
New Contributor

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

gerald_t
New Contributor

Re: Calculate 1 year when only 1 month is selected

Hi Sushil,

Please see attached file. Thanks for the help!

Thanks,

Gerald

sushil353
Honored Contributor II

Re: Calculate 1 year when only 1 month is selected

There is no file attached... please check

Community Browser