Help Needed in Qlik Sense Development

[Ashif]

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.

[rathore01]

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

[anushree1]

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

[Ashif]

HI Anushree,

Please find the below sample data.

Year	Country	Business Unit	Department	Bonus	Staff
2019	C1	BU1	D1	YES	9
2018	C2	BU1	D2	YES	10
2017	C3	BU2	D2	NO	8
2019	C2	BU2	D1	YES	18
2018	C1	BU2	D2	YES	20
2019	C3	BU3	D1	NO	7

Thanks