topic Re: Re: How to calculate expression value based on dimension selected? in QlikView

How to calculate expression value based on dimension selected?

chiranjivdas — Thu, 03 Jul 2014 07:06:52 GMT

I have the following dimension and expression

Dimension = Quarter

Expression = Revenue in $

The actual values are as follows

Q1 2014 = $100

Q2 2014 = $250

Q3 2014 = $375

Q4 2014 = $500

The Quarter is also a part of a multi box selection.

I want to get year to date (YTD) values based on the quarter selected as follows

When Quarter selected = Q1

2014 YTD = Q1 Revenue = $100

When Quarter selected = Q2

2014 YTD = Q1 Revenue + Q2 Revenue = $350

When Quarter selected = Q3

2014 YTD = Q1 Revenue + Q2 Revenue + Q3 Revenue = $725

When Quarter selected = Q4

2014 YTD = Q1 Revenue + Q2 Revenue + Q3 Revenue + Q4 Revenue = $1,225

Much appreciated,

Re: How to calculate expression value based on dimension selected?

tresB — Thu, 03 Jul 2014 07:32:32 GMT

Create a sample qvw and upload here. That would reduce no. of assumptions we have to make; you would be helped faster.

Re: Re: How to calculate expression value based on dimension selected?

chiranjivdas — Thu, 03 Jul 2014 08:03:10 GMT

Attached is the QV. I am looking for the following outcome

Re: How to calculate expression value based on dimension selected?

tresB — Thu, 03 Jul 2014 08:25:53 GMT

Your quarters are strings. Create them in the script using proper date functions like QuarterName() or using dual() along with string so that it can be compared (having a numeric representation at the backend) in the front end expression using date functions or in set analysis.

Re: Re: Re: How to calculate expression value based on dimension selected?

manisha_uit — Thu, 03 Jul 2014 08:31:10 GMT

Hi Chiranjiv,

PFA file and check if this serves your purpose

Thanks,

Manisha

Re: Re: Re: Re: How to calculate expression value based on dimension selected?

chiranjivdas — Thu, 03 Jul 2014 16:26:32 GMT

Manisha,

Your solution works, but I may have oversimplified the data. Attached is how my data really looks like and this is the outcome I am expecting

Re: Re: Re: Re: Re: How to calculate expression value based on dimension selected?

manisha_uit — Thu, 03 Jul 2014 17:17:07 GMT

Hi Chiranjiv,

Since you want to group the cumulative sum based on year, product and quarter, you will have to derive the year field or use it if it already exists

PFA app. Here I have added an additional field - Year

Thanks,

Manisha

Re: Re: Re: Re: Re: How to calculate expression value based on dimension selected?

chiranjivdas — Thu, 03 Jul 2014 19:36:38 GMT

Thanks, Manisha. This is awesome.