Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Last Month Where Data and Date/Text Issue

Dear all,

I have been battling with this problem for a few days and I suspect there is a simple solution.

I have a list of account details with revenue by month but the month data goes to end of 2017 and with 0 where in the future. The current month of data is APR-17 and would not always be the current month or current month-1. I ultimately want to get to a solution where I can do a sum of the revenue for current month versus last month so I can sort it to show largest changes month on month.

I have been trying to create month on month analysis by isolating the current max month where there is data (see variable below) and using the variable as the input for the set analysis (see formula).

I have tried many variations but cannot get the set analysis to accept the variable result. I think it has something to do with the format of the cell but even in varying the solutions around this I get no result, it will only accept if I hard code "APR-2017" as the value.

Variable (MaxMonth)

=AGGR(max({<FACT_MONTHYEAR_DATE={"=sum(AMOUNT)>0"}>} date#(FACT_MONTHYEAR_DATE,'MMM-YYYY')),CLIENT_KEY)

Formula

AGGR(SUM({$<FACT_MONTHYEAR_DATE={$(=MaxMonth)}>}AMOUNT),FACT_MONTHYEAR_DATE)

I have attached my excel source data and a QVD I have been working on. Thanks in advance for any suggestions.

Ben

1 Solution

Accepted Solutions
Highlighted

There were few issues

1) Variable MaxMonth was not created (might have been missed in the sample)

2) MonthYear field was not read as date, but as text

3) some other minor changes

PFA

View solution in original post

3 Replies
Highlighted

There were few issues

1) Variable MaxMonth was not created (might have been missed in the sample)

2) MonthYear field was not read as date, but as text

3) some other minor changes

PFA

View solution in original post

Highlighted
Creator
Creator

Sunny - many thanks, it worked!!

Seems I was close to the solution, and looks like the difference mainly was the setup of the variable in the set analysis.

I researched the syntax on this site before and came across:

SUM({$<FACT_MONTHYEAR_DATE={$(=vMaxMonth)}>}AMOUNT)


but you have corrected this to:

SUM({$<FACT_MONTHYEAR_DATE={"$(=vMaxMonth)"}>}AMOUNT)

i.e. inclusion of the speech marks. What is the effect of the speech mark inclusion (aside from making it work!)

Highlighted

I will give you links which will help you understand this better

A Primer on Set Analysis

Dates in Set Analysis