getting data from the latest month

[#1]

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!

[sunny_talwar · Accepted solution]

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]

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]

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=

[#5]

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!

[#6]

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]

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)

[#8]

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!

[#9]

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!

[lohitseek]

Hello  @sunny_talwar ,

Kindly help me how to  create dimension for every last month & 2 expressions ( Values & Text) .

Text:  X-Axis (Project Names i.e(abc,def,ghi)

Values: Y-Axis( each Project Count i.e (abc(123),def(36) ,ghi(72)).

Every month it should show previous month data 

based on this i need to draw Bar chart . 

Thanks