Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor

Help Needed in Qlik Sense Development

Hi All,
 
We have Year,Country, Business Unit, Department,Bonus dimensions and Staff is the measure in data.
Always Max(Year) should be selected from the "Year" Listbox.
 
Year,Country, Business Unit, Department dimensions are having data, Bonus is a dimension which have only Yes/No values and Staff  having the data.
 
I have two queries mentioned below. Could you please help in this regard at the earliest would be appreciated.
 
1) In a bar chart Country vs Total Staff and reference line in this chart is Avg(Staff) as a baseline.
Query: By selecting any Country, Business Unit OR Department from the filters pane, this reference line value is changing but as per the requirement reference line value should be same (static) for all the Country, Business Unit and Department on Year level.
 
2) In a KPI 
Query: Need to calculate the No. of Staff who took the Bonus in current and previous years by selecting the Year from the Listbox.
For this, I tried to create the flags to get the No. of Staff who took the Bonus in current and previous years but didn't get the desired output.
3 Replies
Highlighted
Partner
Partner

Re: Help Needed in Qlik Sense Development

Hi There,

You can try below set expression

1) In a bar chart Country vs Total Staff and reference line in this chart is Avg(Staff) as a baseline.

Query 1 : By selecting any Country, Business Unit OR Department from the filters pane, this reference line value is changing but as per the requirement reference line value should be same (static) for all the Country, Business Unit and Department on Year level.

Avg({$< Country=, Business Unit =, Department =>} Staff)

2) In a KPI 
 
Query: Need to calculate the No. of Staff who took the Bonus in current and previous years by selecting the Year from the Listbox.
For this, I tried to create the flags to get the No. of Staff who took the Bonus in current and previous years but didn't get the desired output.

 

Considering Flag value as 'YES' for Staff who took the Bonus and 'NO' who didn't

Previous Year - Count({$<Year= {"$(=Max(Year) - 1)"} , Flag = {'YES'} >} Staff)

Current year - Count({$<Year= {"$(=Max(Year) )"} , Flag = {'YES'} >} Staff)

It will be based on your selection to get what is the latest(max) possible year, and minus 1 to get its previous year.

Thanks,

Rahul R

Highlighted
Valued Contributor II

Re: Help Needed in Qlik Sense Development

For the 1st Requirement , you can use 1 in set analysis like avg(1{<your condition>}) or you could use the Function all like avg(all Measure_Name)

2nd one , it would be helpful if you share the sample data

Highlighted
New Contributor

Re: Help Needed in Qlik Sense Development

HI Anushree,

Please find the below sample data.

YearCountryBusiness UnitDepartmentBonusStaff
2019C1BU1D1YES9
2018C2BU1D2YES10
2017C3BU2D2NO8
2019C2BU2D1YES18
2018C1BU2D2YES20
2019C3BU3D1NO7

 

 

Thanks