Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

tunoi
Contributor

Set analysis question

Hi all,

In Variable Overview i have defined the following variable:

e_WP = sum({<LineType = {'Written'}, $(=Replace('$1', ';', ','))>}WP)

Now i want to use this variable in expressions like this

$(e_WP) for current selection which is working

if i use $(e_WP(MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month = )) i get the correct result for YTD

How should i define a new variable like vSetYTD and use as $(e_WP($(vSetYTD)))?

1 Solution

Accepted Solutions

Re: Set analysis question

May be like this:

vSetYTD:  MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =

or

vSetYTD:  =MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =

and then this:

$(e_WP($(=vSetYTD)))

or

$(e_WP($(vSetYTD)))

View solution in original post

6 Replies

Re: Set analysis question

May be like this:

vSetYTD:  MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =

or

vSetYTD:  =MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =

and then this:

$(e_WP($(=vSetYTD)))

or

$(e_WP($(vSetYTD)))

View solution in original post

tunoi
Contributor

Re: Set analysis question

ty, it worked !

tunoi
Contributor

Re: Set analysis question

Now next part of my question.

I will have one year and one month selected. in one chart with year_month dimension i want to display for the last 12 month the YTD for the each of the Year_Month.

How should i define my set analysis variable?

Re: Set analysis question

Each of the Year_Month? Do you have a sample of what you are wanting to do?

tunoi
Contributor

Re: Set analysis question

I have the following table:

   

YearMonthYear_MonthMonthIDWP
201512015_1110
201522015_2220
201532015_335
201542015_4415
201552015_5523
201562015_6621
201572015_7712
201582015_8845
201592015_9946
2015102015_101032
2015112015_111121
2015122015_12125
201612016_11310
201622016_21415
201632016_31520
201642016_41621
201652016_51724
201662016_61845
201672016_71956
201682016_82034
201692016_92123
2016102016_102212
2016112016_112318
2016122016_12246

So if the user select Year = 2016 and Month = 5 my output should be:

 

Year MonthYTD(WP)
2015_694
2015_7106
2015_8151
2015_9197
2015_10229
2015_11250
2015_12255
2016_110
2016_225
2016_345
2016_466
2016_590

Re: Set analysis question

May be using an expression like this:

=RangeSum(Above(Sum({<Year, Month, Year_Month, MonthID>}WP), 0, Only({<Year, Month, Year_Month, MonthID>}Month)))


*


Avg({<Year_Month = {"$(='>' & Date(AddMonths(Max(Year_Month), -12), 'YYYY_M') & '<=' & Date(Max(Year_Month), 'YYYY_M'))"}, Year, Month, MonthID>}1)


Capture.PNG

I have added the breakdown of the two expressions for you to see why it is working the way it is working