How to equate two dates in Set expression

[#1]

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

[datanibbler]

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

[#3]

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

[datanibbler]

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

[#5]

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

[#6]

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

[jagan]

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.

[brijesh1991]

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

Where Date is your Calender Date

[rwunderlich]

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

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

-Rob