Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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:

countDISTINCT{<  $(=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().

3 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Capture.PNG

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)

Capture.PNG

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

Capture.PNG

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

Best,

Sunny