topic count distinct lastyear in QlikView

count distinct lastyear

Tue, 09 Jun 2009 15:44:25 GMT

I have a problem during key linking between our data.

Billing	Order
SalesDoc_ID	%SalesDoc_ID
Billing$.Billing_Type	Order$.Document_Created_Dat
Created_On
Net_Value_Inv

I would like the following

Billing :

1)Sum of (Net_Value_Inv with the Created_On = calendar date (calendar) it is correctly.

Order:

2) Number of the %SalesDoc_ID (distinct) with the following restriction:

2.1) SalesDoc_ID = %SalesDoc_ID

2.2 ) Billing$.Billing_Type='Z2','ZBV','ZCR1','ZEX','ZS1' is stored under BM04.

2.3 ) Order$.Document_Created_Dat = Kalender-Datum = Created_On

count (distinct {$<Kalender.Kalender.Jahr={$(=Only(Kalender.Kalender.Jahr))}>}{$*BM04} if(Order$.Document_Created_Dat = Created_On ,%SalesDoc_ID))

I connected and am provided the field Order$.Document_Created_Dat with the calendars $Syn to 1 Table. Therefore I compare in the formula whether date of order = date from Billing

Only I have the problem with the calendar of Last Year.Like. how can i COUNT formula with 1 year back. (Order$.Document_Created_Dat -1) and (Created_On -1)?

attache QlikView

count distinct lastyear

sathishkumar_go — Tue, 09 Jun 2009 18:38:43 GMT

use macro

sub getselectedfield
set doc = ActiveDocument
set mySelections = doc.fields("Year").GetSelectedValues
for i = 0 to mySelections.Count - 1
set v = ActiveDocument.Variables("Variable1")
v.SetContent mySelections.Item(i).text,true
'v.SetContent mySelections.Item.text,true
next

End sub

Pass this variable in expression.

Example if u select year 2008 variable1 = 2008 so u put in expression Variable1 - 1

I hope it ll help for u.

-Sathish

count distinct lastyear

Tue, 09 Jun 2009 19:35:01 GMT

Hi ,

Thanks, for you Help.

I am working with Qlikview sine 2 months and I don't have any experience with Marko. Do you have any QVW .example for your Marko's? I copied it in my QlikView and don't change any field.

Thanks

Shideh

count distinct lastyear

sathishkumar_go — Tue, 09 Jun 2009 20:37:33 GMT

Un fortunately i don't have any example for macro. Otherwise i ll do 1 thing u attach 1 application. can i write the macro for that app and send to u.Otherwise attach ur application i ll try my level best.

-Sathish

count distinct lastyear

Tue, 09 Jun 2009 20:55:48 GMT

I have modified the application you sent.

Is it what you need ?

The explanation is in the application in the text box on the right

Philippe

count distinct lastyear

Tue, 09 Jun 2009 21:09:07 GMT

Hi Philippe,

Thanks. It is exact what I want.

Thanks again.

Shideh