Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Partner
Partner

Calculation dropping values within a calculated dimension (when making certain selections)

Hi,

I have a synthetic dimension to which separate calculations are to be performed. I'm calculating the count of shipments which are late, beforehand or on time.
Example:

calculated dim1: Last 3 years.
    (Dim2: Late/On time/Beforehand)
        Measure1: If(Dim='Last 3y',
                                    Count({<Year={">=$(=max(Year)-3)<$(=max(Year))"},MonthNum=,YearMonth=>}PONum))

calculated dim1: Last month.
    (Dim2: Late/On time/Beforehand)
        Measure2: If(Dim='Last mo',
                                      count({<YearMonth={"$(=Date(addmonths(max(Date),-1),'YYYYMM'))"},MonthNum=,YearMonth=>}PONum))

calculated dim1: This month.
    (Dim2: Late/On time/Beforehand)
        Measure3: If(Dim='This mo',
                                     count({<YearMonth={"$(=max(YearMonth))"},MonthNum=,YearMonth=>}PONum))

Now, the problems begin when making a selection: I've made it so that none of the shipments are late on May, ever. So when selecting May, the chart drops all values relating to the "Timing" dimension value "Late".
Similarly if I select a YearMonth which doesn't happen to have shipments that are on time, the graph drops all shipments that are on time even for the calculations not referring to the selected YearMonth (in this case 201704).

Capture2.PNG

Here the calculation for the last 3 years is in a separate chart. Why won't the values replicate for the chart on the left?

The same issue arises when making the dimension with ValueList().

Seems like that even when using {1} in the set expression Qlik wont calculate over the full data set when it is within a calculated dimension.

I've tried this with QlikView and Qlik Sense and both seem to have the same issue.

Any ideas? Thanks in advance. (app attached)

1 Solution

Accepted Solutions

Re: Calculation dropping values within a calculated dimension (when making certain selections)

If you can change your calculated dimension on Dim to just Dim... this expression can work for you

If(Only(TOTAL <Dim> {1} Dim) = 'Last 3y',
	count({<Year={">=$(=max({<MonthNum=>}Year)-3)<$(=max({<MonthNum=>}Year))"},MonthNum=,YearMonth=>}PONum)
)

 

image.png

6 Replies

Re: Calculation dropping values within a calculated dimension (when making certain selections)

There was slight difference between the formulas used within the two charts... try this for the L3Y expression on the left chart

If(Dim='Last 3y',
	count({<Year={">=$(=max({<MonthNum=>}Year)-3)<$(=max({<MonthNum=>}Year))"},MonthNum=,YearMonth=>}PONum)
)

image.png

Partner
Partner

Re: Calculation dropping values within a calculated dimension (when making certain selections)

Hi,

Oh yeah, a small mishap from my part. Still wont work.

I could have count({1}PONum) on the left hand chart and the values wont appear. Maybe this is a bug?

Re: Calculation dropping values within a calculated dimension (when making certain selections)

I am confused... I am seeing everything to match based on no selection... is the problem when you make selections?

Partner
Partner

Re: Calculation dropping values within a calculated dimension (when making certain selections)

Yes, like the title suggests.

I made my dummy data so that there are no late shipments in may. I select may, and all the late shipments are lost from the graph, even though the set expression in the calculation specifically states, that ignore month-selection and calculate the last three years. There definitely are late shipments in march 2016 for example, and they wont show up in the graph.

Re: Calculation dropping values within a calculated dimension (when making certain selections)

If you can change your calculated dimension on Dim to just Dim... this expression can work for you

If(Only(TOTAL <Dim> {1} Dim) = 'Last 3y',
	count({<Year={">=$(=max({<MonthNum=>}Year)-3)<$(=max({<MonthNum=>}Year))"},MonthNum=,YearMonth=>}PONum)
)

 

image.png

Partner
Partner

Re: Calculation dropping values within a calculated dimension (when making certain selections)

Wow, seems to be working!

Thank you!