topic Re: Count the records based on column value in QlikView

Count the records based on column value

Fri, 07 Sep 2012 22:03:59 GMT

Hi,

As i'm new to qlikview, i would want to know how to achieve the below scenario in QlikView.

I have a file with 5 columns namely ID, Name, Gender, Year and Promotion, See the attached file

Now in the chart i need to display the count of ID's both Genderwise and Promotionwise per year. Please note that Gender has two values M and F while Promotion has Y and N respectively. To be precise i need to get the count of both male and female ID's for each year, at the same time i need to get the count of the ID's who have got promotion.

I used the below code in the CHART expression :

=count(distinct if( Gender='M', ID) ) >>>>> for Gender

=count(distinct if( Promotion='Y' , ID) ) >>>> for Promotion

I have used the COMBO chart for the same, where Gender is represented in Bar while Promotion Count in Line. But the problem is that, I'm getting the total count(both male and female) for each year instead of the data appearing to be in a stacked format. ( See the snapshot below)

For Eg: If I have 5 records for the year 2010 out of which 3 are M(Male) while the rest are F. In this case while representing in chart, i need to get the data in a stacked format which differentiates the count between male and female for each year respectively.

Can you please assist me ?

Regards,

surman

Re: Count the records based on column value

jagan — Sat, 08 Sep 2012 05:50:53 GMT

Hi,

Please find attached file, hope it helps you.

Regards,

Jagan.

Re: Count the records based on column value

Sun, 09 Sep 2012 15:48:59 GMT

Hi Jagan,

As i'm using a personal edition, i'm not able to open the document sent by you.

Re: Count the records based on column value

whiteline — Sun, 09 Sep 2012 17:57:51 GMT

To make a stacked bar on combo chart you have to use two expressions for bars:

=count(distinct if( Gender='M', ID) )

=count(distinct if( Gender='F', ID) )

For Promotion you should check two conditions:

=count(distinct if( Promotion='Y' and Gender='M' , ID) )

=count(distinct if( Promotion='Y' and Gender='F' , ID) )

Re: Count the records based on column value

Mon, 10 Sep 2012 06:40:27 GMT

Hi,

I dint understand how to use two expressions for BAR and LINE charts seperately ?

Can you please let me know how to do this ?

Re: Count the records based on column value

Mon, 10 Sep 2012 06:40:50 GMT

Hi,

I dint understand how to use two expressions for BAR and LINE charts seperately ?

Can you please let me know how to do this ?

Regards,

surman

Re: Count the records based on column value

Mon, 10 Sep 2012 06:43:04 GMT

Hi,

I dint understand how to use two expressions on BAR and Line Charts seperately ? Can you please let me know how to do it ?

Regards,

surman

Re: Count the records based on column value

Mon, 10 Sep 2012 07:07:07 GMT

Hi,

I dint understand how to use two expressions on BAR and Line Charts seperately ? Can you please let me know how to do it ?

Regards,

surman

Re: Count the records based on column value

Mon, 10 Sep 2012 07:07:31 GMT

Hi,

I dint understand how to use two expressions on BAR and Line Charts seperately ? Can you please let me know how to do it ?

Regards,

surman

Re: Count the records based on column value

whiteline — Mon, 10 Sep 2012 07:46:16 GMT

Create 4 expressions as I've mentioned above.

When you select expression you can toggle checkboxes 'Bar', 'Symbol','Line'.

Toggle appropriate for each expression.

Then select 'Stacked' style on presentation tab.