topic Re: Comparing between two quartes for multiple fields in QlikView

Comparing between two quartes for multiple fields

Tue, 04 Jun 2013 18:00:50 GMT

I get data from Oracle HR. The fields are for example,

1. Exempt_NonExempt ( Values can be Exempt or NonExempt)

2. Sex

3. Emp_Category (Fulltime - regular, fulltime - Contract, parttime - Regurlar, partime-contract)

4. Age

user will pick a quarter and a year and the chart should show the count for the above fields in a bar chart or any other chart.

How to accomplish this.

Thanks for all your help

Re: Comparing between two quartes for multiple fields

Wed, 05 Jun 2013 05:41:49 GMT

Hi Suryan,

PFA first reload the application then show the result hope it is helpful.

Regards,

Nirmal.

Re: Comparing between two quartes for multiple fields

Wed, 05 Jun 2013 08:08:29 GMT

Hello Rsuryan.

What you want can be accomplished through Set Analysis.

I've made two charts, one of which counts data only for the maximum year in current selections and the other for the year before that.

Since the fields are dimensions for the chart, it's better to have one chart for each field or group them via drill-down or cyclic groups.

I've also changed your master calendar a bit.

You can read more about how Set Analysis works in QlikView's aggregation functions here: http://community.qlik.com/docs/DOC-1867

Re: Comparing between two quartes for multiple fields

Thu, 13 Jun 2013 19:12:36 GMT

Thanks.

But, the bar charts should have side by side comparison of the qtr selected with previous year same quarter.

for example: qtr3 of 2011 and qtr3 of 2010.

When I try with the following,

Dimension as GroupedItem (Ethnic_Origin, Emp_Category, Exemp_nonExempt)

Expression Qtr #Prev Year as

UNT({$<QuarterID = {$(=Max(QuarterID) - 4)

Date = {"<=$(vSetCurrentYear)), Max([Month (#)]), Day(Max($(vSetCurrentYear)))),'M/D/YYYY'))"},

Year = ,
Quarter = ,
Period = ,

[Period (#)]

= ,
Month = >} DISTINCT PERSON_ID)

Expression Qtr# Cur Year as

COUNT({<Date={">=$(=Date(Quarterstart(Vmaxdate)))"}>} DISTINCT PERSON_ID)

Both cur and prev shows the same value. Please help.

Re: Comparing between two quartes for multiple fields

Thu, 13 Jun 2013 20:57:49 GMT

Based on the picture from the last post. You can just create two expressions, one for the current year, one for the last. When the user selects a quarter, or more than one, one expression will show information for the current year's quarters, the second for the previous.

Check lowest bar chart in the file attached.

Re: Comparing between two quartes for multiple fields

Fri, 14 Jun 2013 07:17:03 GMT

That's already exactly what the model I posted for you does.

Just select a quarter or several. You can put the expressions in the same chart if you want to.

COUNT({<Year = {$(=max(Year))}>} DISTINCT PERSON_ID) //selected year/last year in selections

COUNT({<Year = {$(=max(Year)-1)}>} DISTINCT PERSON_ID) //the year before the above