Qliksense visualizations accommodating measures based on the attached Notes

[shanmathytitu]

I need to create a table like below with respect to each line of business. Can you please suggest me a visualization to create this in Qliksense and  logic behind to create PY and Change % for the measures. I need to create this for each Line of business like A, B, C,...

I have the below KPI's with me.

New Policies= sum( {<New/Renewal={'New'}>}Policies)

Renewal Policies= sum( {<New/Renewal={'Renewal'}>}Policies)

Total Policies= sum( Policies)

Total Written Premium=sum(Written Premium)

Avg Written Premium=Avg(Written Premium)

Line of Business = A:

KPI	Total	Prior Year	Change
New Policies	235678	200123	12%
Renewal Policies	156123	123145	2%
Rtotal Policies	80632	85376	-3%
Total Written Premium	$20.4M	$19.1M	-4%
Avg Writtritten Premium	$2,100	$1,900	10%

Line of Business = B:

KPI	Total	Prior Year	Change
New Policies	235678	200123	12%
Renewal Policies	156123	123145	2%
Rtotal Policies	80632	85376	-3%
Total Written Premium	$20.4M	$19.1M	-4%
Avg Writtritten Premium	$2,100	$1,900	10%

[Jebrezov]

For PY, you will need to add to your set expression a year component. It will become something like  {<New/Renewal={'New'}, year= {max(year)-1}>}. This type of thing works best when you are filtered down on to one year of data so the visualization can properly perform the PY calculation. The max function is important to ensure one year is pulled and the PY calc is done on just the prior year as well.

I’m honestly new to set expressions myself and can’t remember the exact syntax, but there are a lot of posts that address this and shouldn’t be hard to find. What I like to do is select a couple values in my sheet and go into a formula screen to build my measure and use the insert feature that inserts a set expression based on your current selections. I then modify it to fit my exact needs so I can be sure the syntax is correct.

once you have the PY calc the % change shouldn’t be bad. I don’t believe you can reference master measures in another master measure, but you could assign all these calculations to variables and then reference the variables within the master measures instead. This would save you from having to type out the all the prior formulas again within the % change master measures.

hope that helps a little. Not in a place where I can provide screen shots or more detail right now.