topic Re: Comparing between 2 periods (date and time) in QlikView

Comparing between 2 periods (date and time)

OmarBenSalem — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

As you can see, I want to be able to select 2 periods of date and time to compare.

for example, comparing the 2 periods btwn 01/01/2016 to 12/01/2016 and 01/02/2016 to 11/02/2016 while I'am betwn 9h00 and 10h00.

How to do so?

Should I create 2 fact tables, 2 calendar dates and 2 time tables?

then creating 4 variables; vFromDate1, vToDate1 and vFromDate2, vToDate2 ;each 2 triggering one calendar?

Or could that be done with only one calendar with set analysis?

for example, sthing like this: (can you please verify the syntax with me? I couldn't figure out how to do it)

=Sum([Calendar date]={">=date( $(vFromDate) ,'DD/MM/YYYY')&<=date( $(vToDate) ,'DD/MM/YYYY')"}}Sales)

Thank you very much !

Re: Comparing between 2 periods (date and time)

HirisH_V7 — Sat, 12 Mar 2016 09:29:28 GMT

Hi,

Check this,

BY Creating Four Variables,

vDateFrom1,

vDateto1,

For first period,

vDateFrom2,

vDateto2,

For Second Period,

Using the Calendar objects and by selecting the required Dates for Period 1 and Period 2 ,

With this Expressions in charts or Tables:

=Sum({<Date={'>=$(=Date(vDateFrom1))<=$(=Date(vDateto1))'}>}Sales)

Similarly

=Sum({<Date={'>=$(=Date(vDateFrom2))<=$(=Date(vDateto2))'}>}Sales)

You can acheive like this,

Else another way with out using calendar Object ,You can go with Slider Object too.

Hope this helps,

PFA,

Hirish

Re: Comparing between 2 periods (date and time)

OmarBenSalem — Sat, 12 Mar 2016 10:10:43 GMT

thank you for the reply but I 'm really confused !

I've done as you but I don't seem to get the right thing:

here is the result I'm having:

my data is in 01/01/2016 to 30/01/2016

1) When I choose from date 01 to 08 I have 0 as a result

2) when I reverse it from 08 to 01 I'll have 87918615 as result BUT when I change this to anystinh reversed, for example from 14 to 01, I'll be having the same thing and the same result .

Furthermore:

3) when I choose magasin as a dimension, nothing shows up in the table as shown in the picture above !

what should I do please?

Re: Comparing between 2 periods (date and time)

OmarBenSalem — Sat, 12 Mar 2016 10:39:59 GMT

Re: Comparing between 2 periods (date and time)

HirisH_V7 — Sat, 12 Mar 2016 11:12:55 GMT

Hi,

Can u post your Sample!

Re: Comparing between 2 periods (date and time)

OmarBenSalem — Sat, 12 Mar 2016 12:16:24 GMT

It's way too big; thus I'm downloading it via google drive; and because my connexion is not that good; it might take a little while

Re: Comparing between 2 periods (date and time)

HirisH_V7 — Sat, 12 Mar 2016 12:37:11 GMT

Hi,

please check my modified app in post above,

IF you selected from date once means , You cannot select the date that is below the From date in to date.

Hope this is what you require,

PFA,

Hirish

Re: Comparing between 2 periods (date and time)

OmarBenSalem — Sat, 12 Mar 2016 13:37:23 GMT

Even when I did that; nothing has really changed; I'm always having when I select fromDate < ToDate

But when I select the fromDate > ToDate I'll be having the same result no matter what dates I choose;

I really I'm confused (the sample is still loading...youpi !)

Re: Comparing between 2 periods (date and time)

OmarBenSalem — Sat, 12 Mar 2016 14:38:10 GMT

here is a link to my sample; hope you'll be willing to help me, I'm relly in need ! thank you very much!

historique.zip - Google Drive

Re: Comparing between 2 periods (date and time)

HirisH_V7 — Sun, 13 Mar 2016 07:12:50 GMT

Hi,

I just checked your App and Made some changes,

Go to ->

To Date 1

Min Value -> =Date(Min({1} Date(vDateFrom1)))

Similarly,

To Date 2

Min Value -> =Date(Min({1} Date(vDateFrom2)))

Its working fine.

You have selected for some Dates Ranges where there is no Data,

In second Period 2 Table, Dimension also should be same as Period 1 -magasin

See this,

HTH,

Hirish

Re: Comparing between 2 periods (date and time)

OmarBenSalem — Sun, 13 Mar 2016 10:36:21 GMT

Hi,

Please have a selection with the field Date Ticket; you'll then see that I have data between 10/01/2016 and 30/01/2016

Thus, I don't understand why have such a result (in 2013) ! and when I try to select dates in 2016 I always have 0 as a result !

Re: Comparing between 2 periods (date and time)

OmarBenSalem — Sun, 13 Mar 2016 11:14:09 GMT

Now, when I revert to Date_Ticket(the date of the fact table) instead of Calendar_Date(the date of the master calendar I've created with the Date_Ticket like this:

in the fact table : AutoNumber(Date_Ticket, '%Date ID') as [%Date ID]

in the master calendar: AutoNumber([Calendar date], '%Date ID') as [%Date ID] )

I'll have accurate results ! But why can't I have the same results with Calendar Date I've been using in the simple comparisons (WTD MTD and so on) and having accurate results?

Thank you!

Re: Comparing between 2 periods (date and time)

HirisH_V7 — Sun, 13 Mar 2016 12:36:29 GMT

Hi omar,

Make a sample app with some sample data and Mention your Output required.

I hope that way will be easy to get a desired result.

HTH,

Hirish