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

[#1]

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

[#2]

Hi

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

[#3]

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

[#4]

Hi Sabal,

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

Thanks

[#5]

Hi

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

[#6]

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

[#7]

Hi

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