Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!.
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')
Thanks Manish,
can you please explain,
where i should put above two expression ?
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 ?
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
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.
Can you please provide sample data file ?
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!.
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..
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.