topic Re: Re: How To Calculate Selected Date, Last year date, MTD,YTD in QlikView

How To Calculate Selected Date, Last year date, MTD,YTD

Mon, 21 Jul 2014 14:24:22 GMT

Hi,

I am trying to achieve the following and i did something with the expressions but looks like my values are incorrect.Can someone please throw any suggestions on this?

I am trying to achieve the calculations for

1) TY Traffic ( This Year Traffic for a given date)

2) LY Traffic ( Last Year Traffic for a given date)

3) TY MTD Traffic ( This Year Month To Date( for the whole month until selected date)Traffic for a given date)

4) LY MTD Traffic ( Last Year Month To Date( for the whole month of last year until selected date)Traffic for a given date)

5) TY YTD Traffic ( This Year Month To Date( for the whole year until selected date)Traffic for a given date)

6) LY YTD Traffic ( This Year Month To Date( for the whole last year until selected date)Traffic for a given date)

I have attached a sample excel pivot table also which helps me what values i am looking for and i have attahced my test QVW i am working on, I have selected the date as 20140323 in both QVW and excel for location 3020.So, for these conditions these 2 must match.

The MTD and YTD is same in this case as our Fiscal Month Starts in February .

Can someone please throw any suggestions.

If you have any questions please let me know.

Thanks

Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 13:39:39 GMT

Can you share the sample QVDs?

Thanks

Sabal

Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 13:42:10 GMT

Hi Sabal,

Sorry i didnt get you i dont have any sample QVW's all i have is the excel file which is attached for validation.

Please let me know if you need anything else.

Thanks,

Sujith

Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 13:52:02 GMT

I meant the QVD files from which you are loading data in QVW file in your script.

Files such as:

..\..\Data\QVD\Transform\StoreTraffic_LocWk.qvd (Qvd);

Just a small subset of data would also suffice.

Thanks

Sabal

Re: Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 14:17:58 GMT

Hi Sabal,

Attached are the 3 QVW's i am using in this application.

Please let me know if you need anything else.

Thanks

Re: Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 15:36:20 GMT

Hi Sabal,

Any luck with these ? Sorry to keep you asking but i am already 2 days behind on this work and i need to get it done soon as i have 30 more calculations which basically use the same logic which i need to create.

Thanks

Re: How To Calculate Selected Date, Last year date, MTD,YTD

preminqlik — Wed, 23 Jul 2014 17:40:49 GMT

hi there find attachment and make variables wrto it

hover your mouse on exp to get what exactly happening.

hope this helps you

Re: Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 18:16:18 GMT

Hi Prem,

Looks like we are getting what we want, but in the facts tab if i add sales to traffic the values seems to be getting higher, i think its doing some cross join. Can you please help me with the correct way to write the script for load script to join multiple facts into one?

Attached is my updated QVW.

Also, looks like we are having the calculations for

TY Traffic,

TY MTD Traffic,

LY MTD Traffic,

TY YTD Traffic Traffic,

LY YTD Traffic

I think we are missing LY Traffic, can you also please help me with the calculation for Last Year Traffic.( missing i mean like the value is showing up as 0, where as we do have the value for 2013 traffic)

I really appreciate your time and help.

Thanks

Re: Re: Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 19:07:26 GMT

Sorry for the delay in reply.

Had left from Office.

PFA the QVW file with the revised formulae.

Earlier I had not taken into account the fact that the first day of the week shows the traffic for whole week.

Let me know if still you face any issues.

Thanks

Sabal

Re: Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 20:03:24 GMT

Hi Sabal,

Thanks a lot for your help, it works perfectly fine.However i have 2 last things i want to know,

1) if you look at the attached file in the facts tab i have added sales too but looks like some cross join is happening which is giving wrong values, so can you please help me in changing my load script such that i can add multiple facts to a single one and join them?I just have traffic and sales added for now but i have to add more.

2) the below set analysis

sum({<TransactionChannelDesc ={POS},TransactionTypeId = {1}>}[Units])

gives me the units for POS and tran type 1 what ever we select, however this one will be just for a single day, Can you please help me in creating a MTD for this one?I know i can use the traffic logic, but i am not sure how to embed the current one to that one.

If you can help me with these 2 it would be really great.

Once again thanks a lot for your help.

Thanks

Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 20:20:22 GMT

For the second one, you can use the below set analysis:

sum({<TransactionChannelDesc ={POS},TransactionTypeId = {1}, [Date ID]={">=$(vStartMTD)<=$(vEndDate)"}>}[Units])

I however did not get your first question and the file that you have attached is very big and i am not able to download it.

Thanks

Sabal

Re: Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 20:40:57 GMT

Hi Sabal,

My first question was basically , below is the current script in the facts tab

LOAD

[Date Id],

[Traffic],

[Loc #]

FROM C:\Qlikview\Data\QVD\Transform\StoreTraffic_LocWk.qvd (Qvd);

And this gives us the traffic,now i want to add one more fact to this one and the way i coded was

Sales:

LOAD

[Date Id] ,

[Loc #],

[TransactionTypeId],

[TransactionChannelDesc],

[Transaction Count],

[Units],

[Gross $],

[Discount $],

[Net $],

[Cost $],

[Margin $]

FROM C:\Qlikview\Data\QVD\Transform\Sales_LocWk.qvd (Qvd);

left join(Sales)

LOAD

[Date Id],

[Traffic],

[Loc #]

FROM C:\Qlikview\Data\QVD\Transform\StoreTraffic_LocWk.qvd (Qvd);

And this one if i select SUM(Traffic) its giving me a huge value because it looks like its doing dome cross join and everything.So, can you please help me in writing the load script such that all the facts go into a single table?

Thanks

Re: Re: How To Calculate Selected Date, Last year date, MTD,YTD

Wed, 23 Jul 2014 23:30:53 GMT

Hi Sabal,

Did you get a chance to look at the issue i had?

Thanks

Re: How To Calculate Selected Date, Last year date, MTD,YTD

Thu, 24 Jul 2014 08:02:21 GMT

As I said, I am not able to download your file because of its size.

Can you attach the QVD file: "Sales_LocWk.qvd" so that I can have a look?

Thanks

Sabal

Re: Re: How To Calculate Selected Date, Last year date, MTD,YTD

Thu, 24 Jul 2014 13:49:55 GMT

Hi Sabal,

The Sales_locWk QVD is also way too bigger, but i have posted the question in this forum

Re: Re: How to join multiple facts into a single table

and may be if you can look at it it may give you some ideas?

Please let me know if you have any questions.

Thanks

Re: How To Calculate Selected Date, Last year date, MTD,YTD

Thu, 24 Jul 2014 17:13:34 GMT

You will have to use Join only, concatenate would not work in your case.

And to correct the Traffic value, you can use a counter in "Sales_LocWk.qvd" and divide the value of Traffic by this counter to get the correct value.

Can you attach the same qvw by doing a join between the 2 qvd files?

Thanks

Sabal