Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a text box for sales, location and date. And if select a date and lets say that date has multiple same number of sales ( ex multiple locations have 10,000) it only shows up once.I also created a text object which does the sum of sales and even this one also excludes the duplicates.
Can someone help me with any suggestions on how to get around this one?
Thanks
text boxes usually show one value. but if you are trying to do a count...you may want to add distinct keyword...soemthing like:
=count(distinct Sales)
however, sum(Sales) should give you a sum total of all the sales on that date unless there is something else that is affecting the selection.
if this doesn't help, you may want to paste your expression, your result and the expected result so we can take a closer look at what is going on.
Attached is my QVW i am working on,I am aggregating the sales to the first day of the week and if you see in location 3020 i only see 5 transactions coming out as one is blank and the other one is repeated value however if you change the location to 3021 we can see 7 values and the values add up correctly to what i want to achieve.
Can you throw some light on it?
Thanks
i looked at your file but am unable to see what you mean by only 5 transactions coming out for 3020. the sum([Transaction ID] seems to return the correct total, right?
Nope, if you see the number of values in transaction count we see 5 values but its supposed to be 6 as 18 is repeated twice and i know that a text box will only show 1 value if its duplicated,however if we see in the text object where the value is shown as 128 it should be 128+18 = 146. Even if i am doing the sum the 18 is getting skipped
mee too I don't see the repeated 18 in transaction count
The data is there - create a table box object to see all the data as is in the table - you have Nulls too.
And do samples of data to verify all is correct to compare with the source. if you are not confident, chuck in an Autonumber field on LOAD so you can check if Qlikview is 'compressing' the data to unique rows. This Autonumber field is temporary as a basic check for 1 to 1 records comparation to your source system. Drop it off to free up memory in your final design.
If you create a table box - you will see the data by selections of Loc 3020 and Transaction Count 18 - We see 2 rows only. Sorry I tried to upload my PNG image - it doesnt seem to work for me here.
Also use a Straight Table with Dimension:Loc and Expression:Sum(Transaction Count) - Im sure its totalling ok.
Then clear selections, Click on the Transaction Count=18 from Box table and Loc 3020 - check your sums are correct, ie. 2 dates come up and they sum up .
Hi Peter Pierre when you have duplicate values you first step must be:
1.- Desable all catalogs
2.-Run the script and validating the total of sales
3.-enable one bye one catalog and then run again the script and validate de information may you have some records in some catalog in the joins with your tables that have conflict and duplicate the information is very common this problem
for example:
Sales:
LOAD DateId,
LocationNum,
Text(DateId) & Text(LocationNum) & Text(TranNum) As TranId,
TransactionTypeId,
TranCount,
Quantity,
ExtGrossDollars,
ExtDiscountDollars,
ExtNetDollars,
ExtCostDollars,
Margin
FROM ..\..\..\Data\QVD\Extract\_Sales_2014.qvd(qvd);
/ 1.- *DISABLE ALL CATALOGOS*/
/*
Inner Join (Sales)
LOAD DateId ,
FiscalWeekOfYearId,
FiscalYearId
FROM..\..\..\Data\QVD\Extract\_Date.qvd (qvd);
Inner Join (Sales)
LOAD FiscalYearId,
FiscalWeekOfYearId,
Min(DateId) As DateId
FROM \..\..\Data\QVD\Extract\_Date.qvd(qvd)
Group By FiscalYearId, FiscalWeekOfYearId;
*/
/*2.-Run the script and validating the total of sales*/
/*3.-enable one bye one catalog and then run again the script and validate de information*/
Sales:
LOAD DateId,
LocationNum,
Text(DateId) & Text(LocationNum) & Text(TranNum) As TranId,
TransactionTypeId,
TranCount,
Quantity,
ExtGrossDollars,
ExtDiscountDollars,
ExtNetDollars,
ExtCostDollars,
Margin
FROM ..\..\..\Data\QVD\Extract\_Sales_2014.qvd(qvd);
/* I ENABLE THE SECOND CATALOG*/
Inner Join (Sales)
LOAD DateId ,
FiscalWeekOfYearId,
FiscalYearId
FROM..\..\..\Data\QVD\Extract\_Date.qvd (qvd);
/*LOOK AT THIS I DESABLE THIS LAST CATALOG*/
/*
Inner Join (Sales)
LOAD FiscalYearId,
FiscalWeekOfYearId,
Min(DateId) As DateId
FROM \..\..\Data\QVD\Extract\_Date.qvd(qvd)
Group By FiscalYearId, FiscalWeekOfYearId;
*/
Saw your problem in the Load Script.
You are GROUP BY TranCount so of course you wrill only get 1 instance of TranCount by Loc Num and TransTypeID
So in your source if you have duplicates of TranCount based on your GroubBy Fields only the first 1 is shown while the rest is vetted out.
You best bet to correct this is to replace
TranCount | As [Transaction Count], |
With
Sum(TranCount) | As [Transaction Count], |
Group by (NUM#(TEXT(date(weekstart(date#(DateId,'YYYYMMDD'),0,-1),'YYYYMMDD')))), LocationNum,TransactionTypeId,TranCount;