Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

getting data from the latest month

Hi,

I have a table which contains data from excel spreadsheets from the start of the year until now and another table which reads the latest month's spreadsheet and places it in a separate table. To improve efficiency, i'm planning to remove the second table and use set analysis to grab the latest month data from the first table. As the script will constantly be pulling in new data every month, i need the result to be dynamic.

This is the current expression that i am trying to do:

Count({<Month = {'$(=Max(Month))'}>}Status)

I have a field called status in every spreadsheet and i need to collect the total amount of status, hence i'm using the count function.

Currently all that i'm seeing is a -.

What am i doing wrong?

Also, in the future i might need to separate the status between 'Open' and 'Close', therefore can i use the same expression to separate them?

Thanks!

1 Solution

Accepted Solutions

Re: getting data from the latest month

Check what do you see when you use this in a KPI box object?

=Max(Month)

Is this giving you anything? or showing a number? If it is not showing anything (or '-'), then it might be the issue that Qlik Sense doesn't understand your Month field as a dual month field with underlying numerical value.

You can try this

LOAD Date,

    Month(Date) as Month,

    Num(Month(Date)) as MonthNum,

    ....

FROM ....;

and then this

Count({<MonthNum= {'$(=Max(MonthNum))'}>}Status)

8 Replies

Re: getting data from the latest month

Check what do you see when you use this in a KPI box object?

=Max(Month)

Is this giving you anything? or showing a number? If it is not showing anything (or '-'), then it might be the issue that Qlik Sense doesn't understand your Month field as a dual month field with underlying numerical value.

You can try this

LOAD Date,

    Month(Date) as Month,

    Num(Month(Date)) as MonthNum,

    ....

FROM ....;

and then this

Count({<MonthNum= {'$(=Max(MonthNum))'}>}Status)

agigliotti
Honored Contributor II

Re: getting data from the latest month

try this expression:

sum( aggr( sum( if( Month = max(Month), Status, 0 ) ), Month ) )

to separate the status between 'Open' and 'Close':

sum( {< Status = {"Open"} >} aggr( sum( {< Status = {"Open"} >} if( Month = max(Month), Status, 0 ) ), Month ) )

and

sum( {< Status = {"Close"} >} aggr( sum( {< Status = {"Close"} if( Month = max(Month), Status, 0 ) ), Month ) )

brunobertels
Valued Contributor II

Re: getting data from the latest month

Hi Chan

Sunny is right chek first this expression in a text box and see what happens : "-" nothing or a number or a monthname

In your mesure what is month ? is it a field ? if yes what kind of data does that filed contained ? num ? integer ? Does Qlik recognize it as a date field ?

try the mesure given by Sunny ( All his mesure helps , he is surely one of the best helper here )

For second question of course you can use set analysis for status like that :

Count({<Status= {'Open'}>}Status) will count the status that are OPEN

Count({<Status - = {'Open'}>}Status) see the minus sign before equal sign then it will count all the status that are different from Open

You can also put several set in your mesure like that :

Count({<Month = {'$(=Max(Month))'} , Status= {'Open'}>}Status) will count the status Open for the Max Month

See this link to help you built your set analysis :

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

Not applicable

Re: getting data from the latest month

Hi,

As it is now the weekend i am unable to confirm if you are correct.

However, i believe that if i use the expression

=Max(Month)

it shows me the latest file name eg. Jun-17 as i set the Month field to be the names of the file in ascending order.

As for the rest of the commands, i will try it out when i get back to work on Monday.

Alright I have tried out the script and expression you have given and it works! Combined with bruno bertel's answer i have gotten what i needed.

Thanks for starting me off!

Not applicable

Re: getting data from the latest month

Hi,

I'm new to Qliksense (2 weeks in) with most of my knowledge from reading forums and online help, so i am unable to understand the expression that you have here. The aggr expression has confused me even after reading up on it so can you please give me a short explanation on what the expression does?

As i said in my reply above, i am unable to confirm if your expression works until Monday.

Alright I have tried out the expression but there is no result. If i place the expression in a KPI box object the number comes out to 0.

Thanks!

OmarBenSalem
Esteemed Contributor

Re: getting data from the latest month

the max function works with numeric values; if your month field is a text field (jan, feb..), that won't work; you'll need to create a monthNum field:

Num(Month(Date)) as  monthNum,


and then use it within ur expressions:


Count({<monthNum = {'$(=Max(monthNum))'} , Status= {'Open'}>}Status)


Count({<monthNum = {'$(=Max(monthNum))'} , Status= {'Closed'}>}Status)

Not applicable

Re: getting data from the latest month

Hi,

I don't think Qlik recognises it as a date field as my spreadsheet lists it as text. Using Sunny's method by converting it into a number, it now works.

I have also tried out your expressions that relate to my second question and it works too so it will help me out in the future.

Thanks!

Not applicable

Re: getting data from the latest month

Hi,

after creating a monthNum field and using it within the expressions, it now works! However, as Sunny and bruno answered it earlier i will be marking him as the correct answer.

Thanks!

Community Browser