topic Re: Dates with no data, Current and Avg in QlikView

Dates with no data, Current and Avg

Mon, 02 Nov 2015 16:19:39 GMT

1) I have an object/expression that takes the sum for current(max) Month. I select year '2015', the max month is still December. For Gender I select 'Female' and for country I select 'China', the max Month is then November. Though in this specific case, so far, I need to break up with general QV functionalty (Sorry). I need to be able to click on December, 'Dec', which is max for all data(lets pretent that we are in 2016). It should show '-' for this month as there is no data in December for the mentioned selections.

2) Again Selections are: Gender = 'Female', country='China', Year='2015'.

This other object/expression is an avgerage over the last 6 months aggregated over the sum for each month and year. In this case when I click on December, it should count six month back, so the interval will be from Jul-Dec. There is no data for Aug and Dec, so the the calculation is (Jul,Sep,Okt,Nov,)/4, when December is selected. If there was data for all six month of course it should divide with 6.

Is there a way to solve this without making any extra dummydata in the datamodel?

Re: Dates with no data, Current and Avg

rubenmarin — Mon, 02 Nov 2015 16:36:06 GMT

Hi, not sure to understand the first questtion, for every year the max month will be Dec, why not filtering by this value directly?

Anyway, you can try this:

='Sum current' & '\n' & sum(

{$<Year, Month, Quarter,

[Date2]={">=$(=Monthstart(Max({1<Year={'$(=Max(Year))'}>} [Date2]), 0))"}*{"<=$(=MonthEnd(Max({1<Year={'$(=Max(Year))'}>} [Date2]),0))"}

>} Measure)

For the second question, if there is always 6, use it directly. Change the Avg for a Sum and divide by a fixed '6':

Sum(

{$<Year, Month, Quarter,

[Date2]= {">=$(=Monthstart(Max([Date2]), -6))"}*{"<=$(=MonthEnd(Max([Date2]),0))"}

aggr(

sum({$<Year, Month, Quarter,

[Date2]= {">=$(=Monthstart(Max([Date2]), -6))"}*{"<=$(=MonthEnd(Max([Date2]),0))"}>} Measure)

,YearMonth)

)/6

Re: Dates with no data, Current and Avg

rubenmarin — Mon, 02 Nov 2015 16:41:42 GMT

I edited the first expression

Re: Dates with no data, Current and Avg

Mon, 02 Nov 2015 16:54:17 GMT

Thx, though not what I need.

1) I am talking about the listbox "Month (Date2)", I need to select 'Dec' there, and then get '-' as value. Actually it was the first step to the second problem:

2) so it is related to the first problem, I need to select 'Dec', but not calculating it for 'Dec', but since there is data for the other months in the 6 month back interval, I will get a result . Sometimes there is 4 months with data other times there is 6. Optimal there is 6 month, but is is not fixed, depends on how much data there. Most of the way the avg function takes care of this.

Re: Dates with no data, Current and Avg

Peter_Cammaert — Mon, 02 Nov 2015 17:00:41 GMT

Please remember that in QlikView you are making selections and as a result expressions are walking field values that are associated with your selections. The first request ("clicking values that are not associated with other selections") cannot be implemented without dummy data as clicking on "Dec" will in effect undo your other selections. You will be able to make your expressions ignore the fact that "Dec" has no data through the use of set analysis.

Why do you object to the creation of dummy records with Null values? It is a simple trick to make QlikView behave well by default, and will keep your other expressions simple and maintainable.

Peter

Re: Dates with no data, Current and Avg

swuehl — Mon, 02 Nov 2015 17:04:00 GMT

As you and others said, you will break up with QV standard behaviour / logic, but maybe you can achieve 1) with something like demonstrated here:

Keeping selection list even if no values

Re: Dates with no data, Current and Avg

Mon, 02 Nov 2015 17:16:46 GMT

Thanks Peter, You describe the problem pretty exact.

I just hope to see if anyone can solve it without dummydata, my colleague told me the same, its very unlikely that it can be done. I will ask for the dummydata solution, if there is no other alternatives.

With out dummydata, there is of course less data, though perhaps not significant.

Did I got it right that I can still have both versions in the same application?