Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Attached is my QVF. Below is the Pivot table from the QVF.
The Median, Mode and Max columns are not changing dynamically based on selection of Year and MemberID.
Anyone, Please help!!
Thank you!!
This is due to the data model. The values must also be determined for the years. You can either include the years in the key in the data model or determine them in the diagram as a measure.
Here is an example if you want to solve it via the script. You need each demension to display in the key (just included the meetings in the example).
The script (data editor):
Raw:
Load *,Meeting&Year as KEYYearMeeting
;
Load * inline [
Year|MemberID|Meeting
2021|M01|Online
2021|M01|Phyiscal
2021|M01|Online
2021|M01|Online
2021|M01|Online
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M03|Phyiscal
2021|M03|Phyiscal
2021|M03|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M02|Online
2022|M02|Online
2022|M02|Online
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M02|Online
2023|M02|Online
2023|M02|Online
2023|M02|Phyiscal]
(delimiter is '|');
Raw1:
Load *,
Meeting as M
Resident Raw;
Drop Table Raw;
Output_1:
Load
Year,
M as MT,
Count(M)/NoOfRows('Raw1')* 100 as Per%
Resident Raw1
Group by Year,M;
Output_Mode:
Load MT&Year as KEYYearMeeting,
ModePer%
;
Load
MT,
Year,
Mode(Per%) as ModePer%
Resident Output_1
Group by MT, Year
;
Output_Median:
Load MT&Year as KEYYearMeeting,
MedianPer%
;
Load
MT,
Year,
Median(Per%) as MedianPer%
Resident Output_1
Group by MT, Year
;
Output_Max:
Load MT&Year as KEYYearMeeting,
MaxPer%
;
Load
MT,
Year,
max(Per%) as MaxPer%
Resident Output_1
Group by MT, Year
;
Drop Table Output_1;
The measures :
=sum([ModePer%])
=sum([MedianPer%])
=max([MaxPer%])
By Formula:
=Mode(aggr(Count(Meeting)/Count(Total Meeting),Meeting,Year))
=median(aggr(Count(Meeting)/Count(Total Meeting),Meeting,Year))
=max(aggr(Count(Meeting)/Count(Total Meeting),Meeting,Year))
Script:
Raw:
// Load *,Meeting&Year as KEYYearMeeting
// ;
Load * inline [
Year|MemberID|Meeting
2021|M01|Online
2021|M01|Phyiscal
2021|M01|Online
2021|M01|Online
2021|M01|Online
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M03|Phyiscal
2021|M03|Phyiscal
2021|M03|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M02|Online
2022|M02|Online
2022|M02|Online
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M02|Online
2023|M02|Online
2023|M02|Online
2023|M02|Phyiscal]
(delimiter is '|');
This is due to the data model. The values must also be determined for the years. You can either include the years in the key in the data model or determine them in the diagram as a measure.
Here is an example if you want to solve it via the script. You need each demension to display in the key (just included the meetings in the example).
The script (data editor):
Raw:
Load *,Meeting&Year as KEYYearMeeting
;
Load * inline [
Year|MemberID|Meeting
2021|M01|Online
2021|M01|Phyiscal
2021|M01|Online
2021|M01|Online
2021|M01|Online
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M03|Phyiscal
2021|M03|Phyiscal
2021|M03|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M02|Online
2022|M02|Online
2022|M02|Online
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M02|Online
2023|M02|Online
2023|M02|Online
2023|M02|Phyiscal]
(delimiter is '|');
Raw1:
Load *,
Meeting as M
Resident Raw;
Drop Table Raw;
Output_1:
Load
Year,
M as MT,
Count(M)/NoOfRows('Raw1')* 100 as Per%
Resident Raw1
Group by Year,M;
Output_Mode:
Load MT&Year as KEYYearMeeting,
ModePer%
;
Load
MT,
Year,
Mode(Per%) as ModePer%
Resident Output_1
Group by MT, Year
;
Output_Median:
Load MT&Year as KEYYearMeeting,
MedianPer%
;
Load
MT,
Year,
Median(Per%) as MedianPer%
Resident Output_1
Group by MT, Year
;
Output_Max:
Load MT&Year as KEYYearMeeting,
MaxPer%
;
Load
MT,
Year,
max(Per%) as MaxPer%
Resident Output_1
Group by MT, Year
;
Drop Table Output_1;
The measures :
=sum([ModePer%])
=sum([MedianPer%])
=max([MaxPer%])
By Formula:
=Mode(aggr(Count(Meeting)/Count(Total Meeting),Meeting,Year))
=median(aggr(Count(Meeting)/Count(Total Meeting),Meeting,Year))
=max(aggr(Count(Meeting)/Count(Total Meeting),Meeting,Year))
Script:
Raw:
// Load *,Meeting&Year as KEYYearMeeting
// ;
Load * inline [
Year|MemberID|Meeting
2021|M01|Online
2021|M01|Phyiscal
2021|M01|Online
2021|M01|Online
2021|M01|Online
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Phyiscal
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M02|Online
2021|M03|Phyiscal
2021|M03|Phyiscal
2021|M03|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M01|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Phyiscal
2022|M02|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M03|Online
2022|M02|Online
2022|M02|Online
2022|M02|Online
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M01|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Phyiscal
2023|M02|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M03|Online
2023|M02|Online
2023|M02|Online
2023|M02|Online
2023|M02|Phyiscal]
(delimiter is '|');