topic Re: Getting records as per currect selection date in QlikView

Getting records as per currect selection date

deepakqlikview_123 — Thu, 24 Apr 2014 07:17:15 GMT

Hello All,

I have two fields in my database Updatedon date and invoice date.

First of all I need to count record as per updatedon date and if updatedon date not exists records as per count invoice date

I want to count all records in retail for respective month and year based on current selection month and year,

Can you please suggest at code level what modifications are required for this.

Thanks

Re: Getting records as per currect selection date

Thu, 24 Apr 2014 07:35:34 GMT

Deepak,

You are talking about modifications. Please share the application you want to modify...

regards

Darek

Re: Getting records as per currect selection date

deepakqlikview_123 — Thu, 24 Apr 2014 07:38:31 GMT

Hi Dariusz,

As application is too huge not able to share.

Can you please suggest how to count record in above mentioned case.

Thanks

Re: Getting records as per currect selection date

Thu, 24 Apr 2014 07:49:27 GMT

It is difficult for me to answer your question if i cant see application, data model etc.

I uderstand, that in your qlikview table you have those data fields.

Having regard to the selection of the user you want to count the number of records in which at least one of these dates there? or maybe you want to count records with a specific first date as a first indicator and records that do not have a first date but they have other as yet another indicator?
Please describe in more detail at least your expectations. And the best Annex section of the application or .xls with sample data or anything that will allow clearly read your needs ...

regards
Darek

Re: Getting records as per currect selection date

deepakqlikview_123 — Thu, 24 Apr 2014 08:32:24 GMT

Hi Dariusz,

I want to count first updatedon date and when updated on date is null then count as per invoice date.

Moreover there is also a flag field while counting I have to count all fields where IsCancelled flag='0'

Thanks,

Re: Getting records as per currect selection date

Thu, 24 Apr 2014 08:46:10 GMT

And you have those fields in the same table?

Re: Getting records as per currect selection date

deepakqlikview_123 — Thu, 24 Apr 2014 08:47:32 GMT

Yes I have same fields in same table.

Re: Getting records as per currect selection date

PrashantSangle — Thu, 24 Apr 2014 08:48:32 GMT

Hi,

When I read your discussion, I understand that you want to count Updated_On_Date

if it is null then you are counting invoice date.

Try this,

Create a flag field in your script as

if(isnull(Updated_On_Date),'true','false') as updated_On_Date_Flag;

Then In your chart write set analysis.

Like,

if(updated_On_Date_Flag="true",count({<updated_On_Date_Flag={"true"}>}Updated_On_Date),count(invoice_date))

Regards,

Re: Getting records as per currect selection date

Thu, 24 Apr 2014 08:52:42 GMT

If you have both dates in the same table you may create additional field calculated from those dates during reload.

Simple:

if(isnull(UpdatedonDate), InvoiceDate, UpdatedonDate) as CountDate,

and then you will count on this CountDate.

You may also do similiar thing on the fly, using calculated dimension.

About IsCancelled flag='0' - use set analysis...

count({<IsCancelled={0}>}value)

regards

Darek

Re: Getting records as per currect selection date

deepakqlikview_123 — Thu, 24 Apr 2014 08:59:45 GMT

Hi Dariusz,

Can you pleasetell me what would be final expression I am still getting error.

Thanks

Re: Getting records as per currect selection date

Thu, 24 Apr 2014 09:03:27 GMT

Deepak,

this is why it is difficult to help when i cant do it in your sample application

Having it, i can even do it for you. But if i have not it, i can only give you idea, how to do that ....

regards

Darek

Re: Getting records as per currect selection date

deepakqlikview_123 — Thu, 24 Apr 2014 10:31:17 GMT

Hello All,

If value for updatedon date is '-' not null then how to proceed below

I count first updatedon date and when updated on date is '-' then count as per invoice date.

Moreover there is also a flag field while counting I have to count all fields where IsCancelled flag='0'

Thanks,

Re: Getting records as per currect selection date

deepakqlikview_123 — Thu, 24 Apr 2014 10:48:41 GMT

Hi,

If value for updatedon date is '-' not null then how to proceed below

I count first updatedon date and when updated on date is '-' then count as per invoice date.

Moreover there is also a flag field while counting I have to count all fields where IsCancelled flag='0'

Thanks,

Re: Getting records as per currect selection date

deepakqlikview_123 — Thu, 24 Apr 2014 10:49:31 GMT

Hi,

If value for updatedon date is '-' not null then how to proceed below

I count first updatedon date and when updated on date is '-' then count as per invoice date.

Moreover there is also a flag field while counting I have to count all fields where IsCancelled flag='0'

Thanks,

Re: Getting records as per currect selection date

Thu, 24 Apr 2014 10:52:36 GMT

Try this,

Create a flag field in your script as

if(isnull(Updated_On_Date) OR Updated_On_Date='-','true','false') as updated_On_Date_Flag;

Then In your chart write set analysis.

Like,

if(updated_On_Date_Flag="true",count({<updated_On_Date_Flag={"true"}>}Updated_On_Date),count(invoice_date))

credit max

Re: Getting records as per currect selection date

deepakqlikview_123 — Thu, 24 Apr 2014 12:01:33 GMT

Hi All,

Please correct me if I am wrong,

if(isnull(UpdatedonDate), InvoiceDate, UpdatedonDate) as CountDate,

for counting countDate in same expression is it correct

count(if(isnull(UpdatedonDate), InvoiceDate, UpdatedonDate) CountDate) as count,

Re: Getting records as per currect selection date

Thu, 24 Apr 2014 12:04:57 GMT

You appear to be missing the OR statement

Re: Getting records as per currect selection date

PrashantSangle — Thu, 24 Apr 2014 12:50:07 GMT

Hi,

Try this,

In above solution, there i need to do some change like below.

This will help you.

if(isnull(Updated_On_Date) OR Updated_On_Date='-' ,'true','false') as updated_On_Date_Flag;

then your set analysis is like,

if(updated_On_Date_Flag="false" and IsCancelled_flag='0' ,count({<updated_On_Date_Flag={"false"}>}Updated_On_Date),count(invoice_date))

Regards,