topic Creating a COUNTIFS with multiple variables in Qlikview in QlikView

Creating a COUNTIFS with multiple variables in Qlikview

akidhossain — Thu, 01 Aug 2019 13:59:02 GMT

Hello! I am very new to Qlikview and am trying to do recreate a formula that I typically do in Microsoft Excel. I have a table that looks like something below:

	COL A	COL B	COL C	COL D
ROW 1	SKU	Region	Manager	Region Manager Count
ROW 2	SKU1	US	Joe	2
ROW 3	SKU1	EU	Harry	2
ROW 4	SKU2	ASIA	Randy	2
ROW 5	SKU2	US	Mike	1
ROW 6	SKU2	CAN	Nathan	1
ROW 7	SKU3	US	Joe	2
ROW 8	SKU4	EU	Harry	2
ROW 9	SKU4	ASIA	Randy	2

The Excel version of the formula I have in Column D is: =COUNTIFS(B:B,B2,C:C,C2). Is there a way to create an expression in a Chart that does the same thing in Qlikview? Thank you!

Re: Creating a COUNTIFS with multiple variables in Qlikview

m_woolf — Thu, 01 Aug 2019 16:59:20 GMT

When loading your data, create a new field:
Region & '|' & Manager as key_Region_Manager,

After loading your data, something like this:

Temp:
Noconcatenate
load
key_Region_Manager,
count(key_Region_Manager) as Reg_Mgr_Cnt
resident Data
group by key_Region_Manager;

Then:
left join(Data)
load *
resident Temp;

drop table Temp;

Re: Creating a COUNTIFS with multiple variables in Qlikview

rubenmarin — Thu, 01 Aug 2019 17:07:19 GMT

Hi, you can try with: Count(TOTAL <[COL B], [COL C]> [COL C])

Re: Creating a COUNTIFS with multiple variables in Qlikview

Brett_Bleess — Thu, 22 Aug 2019 20:44:45 GMT

Khandker, did either of the poster's comments help you get things working? If so, please consider using the Accept as Solution button to give them credit for the effort. If you did something else, you can share that as well and mark that as the solution as well.

Regards,
Brett