Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to check if Record exist in Previous Month ?

Hi all,

I am new to qlikview.

I have below questions.

let say,

we have records for every month.

Record    ReportMonth

    1             Jan - 2014

    1             Feb-2014

     1            Apr - 2014

Let say,

We have ReportMonth as List box as selection cirteria for user.

if user select

Report Month Apr - 2014 then

we can to check and display that record was exist in previous month or not.

so,

if they select

Apr - 2014, then we need to check for previous month Mar - 2014,

if it does not exist then need to show - 'N'

if it exist then need to show = 'Y'

how can check this ?

Thanks!.

20 Replies
MK_QSL
MVP
MVP

Use Below Script...

=================

Load Record, Date#(ReportMonth,'MMM-YYYY') as ReportMonth Inline

[

  Record, ReportMonth

    1, Jan-2014

    1, Feb-2014

    1, Apr-2014

];

=====================================

Now use ReportMonth as a List Box and below two expressions for your desired results..

='Data Exists For Selected Month : '&IF(Count({<ReportMonth = {"$(=Date(Max(ReportMonth),'MMM-YYYY'))"}>}Record)>0,'Yes','No')

='Data Exists For Previous Month : '&IF(Count({<ReportMonth = {"$(=Date(Max(ReportMonth)-1,'MMM-YYYY'))"}>}Record)>0,'Yes','No')

Not applicable
Author

Thanks Manish,

can you please explain,

where i should put above two expression ?

Not applicable
Author

Thanks Manish,

I have tried second expression,

it looks like that partially it is working,

we are getting duplicate records for the selected records

i believed,

we are getting record from current month as wells as  previous month.

we just need flag whether current month reocrds was exist in previous month or not?

can you please help with that ?

Not applicable
Author

I have attached an example where the calculation is done on the back-end. I created a flag that will check if whether a previous month exists or not. If exists then 'Y' else 'N'.

Hope it helps

Thanks

AJ

Not applicable
Author

Ajay, Thanks.

i think, you forgot attachment.

also, alternatively,

do you know how can we implement on qlikview front end side ?

Thanks for your help.

MK_QSL
MVP
MVP

Can you please provide sample data file ?

Not applicable
Author


Manish,

Below is my Sample Example.

Product             ReportMonth          <----Database Records in QVD

  ABC                     Jan-2014

  EFG                     Jan-2014

  ABC                     Feb-2014

  EFG                     Feb-2014

  ABC                     Mar-2014

  EFG                     Mar-2014

  EFG                      Apr-2014

  HIJ                        Apr-2014

Let Say,

We are showing this information in below layout in QV layout presentation layer.

We have ReportMonth in the Selection Box as below.

ReportMonth

    Jan-2014

    Feb-2014

    Mar-2014

    Apr-2014

We are presenting below information in Straight Table.

if we select Feb-2014 from Report Month Selection Box,

it would show below.

      Product       New?

        ABC          No  <---because this product was present in previous month Jan-2014

        EFG          No

if we select Apr-2014 from Report Month Selection Box,

it would show below.

      Product       New?

        EFG          No  <--Because this product was present in previous month Mar-2014

         HIJ           Yes <--Because this product was Not present in previous month Mar-2014 and it's New Product in Apr-2014

Basically,

Product from the Selected Report Month,

we need to check for those product in the previous Report Month from selected month,

if Product Exist in previous month then,

Flag that as Not New,

else Flag Flag that as New.

above is our example Criteria.

Please Let me know, if you have any question or need more information.

Really Appreciate your Help and Time on this.

Thanks!.

MK_QSL
MVP
MVP

Use below table in script..

============================

Load
Product,
Date#(ReportMonth,'MMM-YYYY') as ReportMonth
Inline
[
Product,       ReportMonth
ABC,        Jan-2014
EFG,        Jan-2014
ABC,        Feb-2014
EFG,        Feb-2014
ABC,        Mar-2014
EFG,        Mar-2014
EFG,        Apr-2014
HIJ,        Apr-2014

]
;

=============================

Now create a chart with below dimensions and expressions

Dimension

Calculated Dimension

=IF(NOT IsNull(ReportMonth),Product)

Expression

COUNT(DISTINCT Product)

IF(COUNT({<ReportMonth = {'<$(=Max(ReportMonth))'}, Product = P({<Product>})>}Product)>0,'NO','YES')

Go to presentation tab

Select 1st Expression and tick Hide Column…

Hope this helps..

Not applicable
Author

Thanks Manish,

I will try this and let you know.

do we need to use Calculated Dimension ?

our environemnt does not allow to use calculated Dimension.

is there any other alternative ?

Thanks for your help.