topic Re: How to equate two dates in Set expression in QlikView

How to equate two dates in Set expression

Thu, 09 Jan 2014 11:07:03 GMT

Hi All,

As per the requirement, i want to show the number of products placed and delivered on the same month.

If order placed=100 (dec)

and order delivered =70(dec) 70 orders should be among 100 orders placed in dec

I want the percentage of order delivered/ order placed.

i.e, Order placed and order delivered in dec/ order placed

We can do this comparing two different dates, orderplaced date=dec, and orderplaced and delivered=dec,

Am not able to achieve this,

Can anyone help me to solve this.

Thanks and Regards,

Smitha

Re: How to equate two dates in Set expression

datanibbler — Thu, 09 Jan 2014 11:11:26 GMT

Hi Smitha,

can you give an example of how your date_placed and date_delivered look like?

Generally, that should work using set_expression. You just have to make sure both dates have the same format so QlikView can compare them.

HTH

Best regards,

DataNibbler

Re: Re: How to equate two dates in Set expression

Thu, 09 Jan 2014 11:56:00 GMT

hi,

I really dont have to compare both the dates.

I want to have the order count placedin particular month and the same orders which are delivered on the same month.

If 100 orders placed in Dec, 70 out of 100 delivered in dec

i need to calculate the percentage of 70/100

I am attaching xls for your reference.

let me know if you need any more information concern to above issue.

Thanks and Regards,

Smitha

Re: How to equate two dates in Set expression

datanibbler — Thu, 09 Jan 2014 12:38:45 GMT

Hi Smitha,

well, how do you want to define whether or not the two dates are in the same month without comparing them? 😉

As I said, you have to make sure they both have the same format using the DATE() function. Better use the floor() function, too to cut off that time_part.

Then you can cut the year_month_part out of that to make sure the two dates are in the same year and the same month.

Try something like

MID(DATE(floor([placed]), 'DD/MM/YY'), 4, 5) - that will give you sth like >>> 09/13 <<<

You would need two formulas like this in your set_expression - one for each of your dates. Unfortunately I don't know how to do that - I'd suggest re-formatting (or even both) of the dates like that in the script - test it first, e.g. in a textbox or in a listbox_object before putting it in the script. Then you'll have one or two native fields that you can easily compare (set_to_be_equal) in a set_expression.

I think once you have the nr. of orders placed and delivered in the same month, getting the percentage of the orders_placed will be easy.

HTH

Best regards,

DataNibbler

Re: How to equate two dates in Set expression

Fri, 10 Jan 2014 02:59:50 GMT

I can handle the date format, the main challenge is how to put the condition orderplaced and order delivered in the same month.

Thanks and Regards,

Smitha

Re: Re: How to equate two dates in Set expression

Fri, 10 Jan 2014 04:05:10 GMT

Use IF condition instead of Set analysis.

For your scenario, you need to verify Row wise, I don't think set analysis will do this.

Karthik

Re: How to equate two dates in Set expression

jagan — Fri, 10 Jan 2014 04:19:15 GMT

Hi,

Arrive a flag in script like this

LOAD

If(MonthName(Orderdelivered) = MonthName(Orderplaced), 1, 0) AS Flag

FROM DataSource;

Now in expression just use

=Count({<Flag={1}>}Order_ID)

Hope this helps you.

Regards,

Jagan.

Re: How to equate two dates in Set expression

brijesh1991 — Fri, 10 Jan 2014 04:45:40 GMT

Count({$<Date={=$(=OrderPlaced=OrderDelivered)}>}Order_ID)

Where Date is your Calender Date

Re: How to equate two dates in Set expression

rwunderlich — Fri, 10 Jan 2014 05:43:27 GMT

After formatting both dates to a MonthName, as others have suggested, I believe the SA syntax will be

=Count({<OrderMonth = DeliveryMonth>} OrderId)

-Rob