Cannot show a minichart with valuelist and aggr() in the expression

[#1]

I have a KPI sheet with a straight table that is built with a valuelist. One of the KPI's that I am monitoring is the number of suppliers with a spend per month below a certain limit (50 in the example below). Each supplier can have more than one entry, so I need to use sum() to get the total per month.

It works like a charm to get the result for the current month by using the expression below:

count( DISTINCT{< $(vCurrentMonthSet), SupplierNumber*= {"=sum( {<$(vCurrentMonthSet)>} Spend)<50"} >} SupplierNumber)

However, I also want to see the trend in a minichart and at first I just copied the expression above (replacing current month with last 12 mths) and put the dimension in the chart to be SpendYearMonth as below:

count(  DISTINCT{<  $(=vLast12Months),SupplierNumber*= {"=sum( {< $(=$(vLast12Months)) >} Spend < 50"} >} SupplierNumber)

But that gave the wrong result. I scratched my head and didn't really understand why at first. But then I came to the conclusion that it was summing the spend for all 12 months and seeing if it was below 50 (accumulated) and that was not the same thing as I was looking for.

Then I decided to calculated the sum using aggr()  and seeing which came below 50 per month.

sum( { < $(=vLast12Months)>} aggr( if( Sum( {< $(=vLast12Months)>}  Spend < 50,1,0),SupplierNumber,SpendYearMonth))

And that worked! I first tried it in a straight table with SpendYearMonth as the dimension and then in a minichart - both worked fine. So all was jolly good till I added the next expression in the valuelist. Then nothing showed up in the chart! I've searched on the site and as far as I understand there seems to be some problems with valuelist and aggr(), but I don't know if that is the case (can't find anything in the help file).

So now to my question: Is there any possibility to get a trend in a minichart (with other expressions as well)? Either by making aggr() work or by finding the suppliers without having to use aggr().

[Gysbert_Wassenaar]

Can you post a small qlikview document that illustrates the problem?

[#3]

Hi Gysbert,

Please see the attached qvw. As you can see from the minicharts, the aggr() only works when it is the first expression. So if I could achieve the same result either by not using aggr() or by making aggr() to work in QV.

I use QV 11, SR10.

[sunny_talwar]

Before we move forward I want to make sure that this is not a version issue. I am seeing this

I have not changed anything except replacing them. I hope you are seeing them also and the issue is related to the values these three objects are displaying, right?

If that is the case, I make a slight modification in your script to make Set analysis work

New Script:

LOAD *,

  MonthName(SpendMonth) as SpendMonthName,

AutoNumber(SupplierNumber&'|'&MonthName(SpendMonth)) as Key;

LOAD SpendMonth,

    SupplierNumber,

    Spend

FROM

[\\\Indata spend.xlsx]

(ooxml, embedded labels, table is Sheet1);

Now use this expression:

Count(DISTINCT {<Key *= {"=sum(Spend)<50"}>} SupplierNumber)

Without selection issue should now be fixed

Now with regards to the minichart, I think it seems to be working fine with the Aggr(), but if for some reason your version doesn't show a mini chart, then set analysis version shows it the same way now

I am attaching the qvw for you to check if you see these minichart lines or not.

Best,

Sunny