Highest Count for Previous Month Based on List Box Selection

[Deepty]

Hi All,

I want to calculate the highest count for previous months based on my list box selection in a text box.
My list box selection is of Month-Year . For eg: 03-2021.So, when I select 03-2021 I should get the highest value for 02-2021.

Currently the highest count is being calculated like this:

NUM(Max(AGGR(sum(EUC),date)),'#,##0') 

I calculated the max for previous month using below logic:

=NUM(Max(AGGR(sum(EUC),$(VCR_PrvMonth))),'#,##0')

where VCR_PrvMonth is a variable which gives previous month below is the logic:

=Date((AddMonths(max(DATE),-1)),'MM-YYYY')

But this gives me '-' as the ouput.

I have also tried using below logic:

=MAX(aggr(SUM({<MonthYear={">=$(VCR_Monthstart)<=$(VCR_MonthEnd)"} >} EUC),DATE)) this also gives '-' as the output.

Can you please help here.

[lenka_kruse]

Hi Deepty,

I just spent a frustrating half hour trying out all my set analysis mojo - but what did the trick in the end was applying the set analysis function to both statements, the max() and the aggr(). This did the trick for me:

=num( max(  {$<Month_No = {$(=max(Month_No)-1)}, Month_Year=>}
          aggr(  {$<Month_No = {$(=max(Month_No)-1)}, Month_Year=>}
                        sum( EUC), Datum)),'#.##0')

And I had created a running number for the distinct months (Month_No), so that I could easily substract one for the formula. And don't forget to add all date fields that the expression needs to disregard to the list - in my case, it was only "Month_Year=". If you don't deactivate their selection in the set-analysis-statement, the statement will still give NULL as output.

Hope this works for you!

[Deepty]

Hi Lenka,

Thank you for taking out the time to reply, I really appreciate!!

Regarding the solution , I was trying to implement the logic you suggested using my own variables but I get errors in expression :

=num( max(  {$<Month_No = {$(=max(Month_No)-1)}, Month_Year=>}
          aggr(  {$<Month_No = {$(=max(Month_No)-1)}, Month_Year=>}
                        sum( EUC), Datum)),'#.##0')

I am using version 12.3 of QlikView. Is there anything which needs to be added in this set expression? If this works for you but for me it says errors in expression.

Thanks

[lenka_kruse]

Hi,

did you replace all the calendar field names with the fieldnames in your own data model? By the way, it always helps to attach a qvw with your own data, because then people can test it directly and give you a solution that already contains all your own field names.

The field "Month_No" you have to create in your own data model, as a running number for each distinct month, so that it ends up like this:

Date	Month_Year	Month_No
01.11.2020	11-2020	1
02.11.2020	11-2020	1
03.11.2020	11-2020	1
…	11-2020	1
30.11.2020	11-2020	1
01.12.2020	12-2020	2
02.12.2020	12-2020	2
…	12-2020	2
31.12.2020	12-2020	2
01.01.2021	01-2021	3
02.01.2021	01-2021	3
…	01-2021	3
31.01.2021	01-2021	3
01.02.2021	02-2021	4
02.02.2021	02-2021	4

And where I have named "Month_Year=" as a field where the selection has to be disregarded, you need to add other fields from your calendar-table, to make sure that their selection doesn't mess with your formula.

As general advice: It's better not to use QlikView-skript commands as field names, like with "date", or "month" - always make it something like "SalesDate", to prevent ambiguity. Just like you shouldn't name expressions in charts after field names, because then the chart won't know if you mean the field or the expression.