topic Expression with Altered State in QlikView

Expression with Altered State

Mon, 12 Jan 2015 20:34:05 GMT

Hello,

I need to create a table that compares two time periods. The end user can define "current period" and "prior period". The user should have the option of comparing the following:

* Current [Month] to Previous [Month]

* Current [Quarter] to Previous [Quarter]

* Current [Year] to Previous [Year]

I have established that the best way to handle this is by using altered states so I have created a "Group 1" which will be Current Period and "Group 2" which will be Prior Period.

My table should ultimately look like this, where the user selected Q4 2014 as Current Period, as an example, and Q3 2014 for Prior Period.

Metric	Prior Period Q3 2014	Current Period Q4 2014	Difference
Total Dollars	$100,000	$130,000	30.0%
Total People	347	301	-13.3%

Metric

Prior Period

Q3 2014

Current Period

Q4 2014

Difference

Total Dollars

$100,000

$130,000

30.0%

Total People

347

301

-13.3%

I will need to have separate filters for each group. Both filters will include the same information but the user can select different dates to compare.

My filter:

Multi Box that includes a three drop downs options; one for year, one for QuarterYear, and one for MonthYear.

So the user should only be able to select one of the three options within one group. A Year or a QuarterYear, or a MonthYear (i.e. 2014 or Dec 2014 or Q4 2014)

Is this possible to do? Can someone help me out with the expressions that I need to use? One expression will be in the column header to make the date reference dynamic and the other would be in the cells in each column to actually add data just from the selected time period.

I'm tackling this one step at a time so this is the next step to my first question on how to achieve the prior / current comparison...answer was to user alternate states, which I'm now trying to do:)

Thank you so much!

Re: Expression with Altered State

reshmakala — Mon, 12 Jan 2015 20:58:34 GMT

Hello,

am not sure if this is what you are looking. Please look at the attached file. Let me know if you have a different question.

Re: Expression with Altered State

Mon, 12 Jan 2015 21:30:04 GMT

Your example is exactly what I was looking for. Thank you!

I am, however, having trouble implementing it with my data. Do you know what might be wrong with this expression?

count(DISTINCT({Group 2}[LocationID]))

I'm getting an error with the opening squiggly bracket...not the closing.

Re: Expression with Altered State

reshmakala — Tue, 13 Jan 2015 14:28:22 GMT

You are welcome. Sorry for the delay. There is a small correction in your expression.

Please try

count ( {Group2} DISTINCT (LocationID) )

Let me know if that doesn't work.

Re: Expression with Altered State

Tue, 13 Jan 2015 15:09:38 GMT

It looks like the syntax is correct however it's returning null and the answer should be 171. Any thoughts on why it would return null?

This is what I entered as the expression...

count({[Group 2]}DISTINCT[LocationID])

Re: Expression with Altered State

reshmakala — Tue, 13 Jan 2015 15:12:17 GMT

Can you attach a file with sample data?

Re: Expression with Altered State

reshmakala — Tue, 13 Jan 2015 15:14:29 GMT

See this

Re: Expression with Altered State

Tue, 13 Jan 2015 15:40:39 GMT

I am not seeing a file if you attached one with your "see this" post...BUT I was just playing around with it and somehow I tweaked something and it works!! Not sure what the magic change was but what matters is it is returning the correct amount.

Thank you so much, Reshma. Your example is what helped me through it.

Re: Expression with Altered State

Tue, 13 Jan 2015 15:49:59 GMT

One other question, Reshma...in your example you have one row of data. How would you set it up if you needed to have several rows of data where you show the group1 and group2 results in separate columns?

Re: Expression with Altered State

reshmakala — Tue, 13 Jan 2015 16:15:07 GMT

In my example, I just did a summation of data values, when you use pivot table and have multiple rows, you would be able to find the same with you current selections.

Re: Expression with Altered State

reshmakala — Tue, 13 Jan 2015 16:15:26 GMT

You are welcome!

Re: Expression with Altered State

Tue, 13 Jan 2015 21:14:27 GMT

Okay so it sounds to me like this can't be done in a straight table. I will swap it out.

Again, thank you for your help, Reshma.

Re: Expression with Altered State

reshmakala — Wed, 14 Jan 2015 14:54:41 GMT

You're welcome! Happy learning!