Weekend counts

[smilingjohn]

HI All , 

I have to show the total counts by Weekend till today even if there is no date for that . 

What I mean is I have a field call "Date" from which I am getting  Weekend and also using the master calneder to generate the missing dates . But i am not able to generate it till today . 

Say for example i have  2 counts on weekend 12-October 2019 and the next count is 5  on  30-Nov-2019 then my Count should  show as 2 from 12-Oct-2019 till  23-Nov-2019.  Which I am not able to get this .  Can please someone help me on this ?  

I am attaching my QVW file for refrence . 

[Kushal_Chawda]

try this

Trend:
LOAD ID,
Status,
Date

FROM
[C:\trend.xlsx]
(ooxml, embedded labels, table is Sheet1);

Calendar:
LOAD

Date(MinDate + IterNo() -1,'DD/MM/YYYY') as Date,
WeekEnd(date(MinDate + IterNo() -1,'DD/MM/YYYY')) as Weekend,
Date(MinDate + IterNo() -1,'YYYY') as Year,
Date(MinDate + IterNo() -1,'MM/YY') as Month,
Date(MinDate + IterNo() -1,'YY') & '_' & Num(Week(MinDate + IterNo() -1),'00') as Week

While
MinDate + IterNo() -1 <=MaxDate;

LOAD
min(Date) as MinDate,
floor(today()) as MaxDate

Resident Trend;

[smilingjohn]

@Kushal_Chawda  you are just awesome sir, 

Now this is generating the the dates till today . 

I tried with my actaul forlmula as shown belwo which is not giving me correct result when i add status ? Can you please correct this ?

sum(aggr(if(count({<Status={'Open'},Weekend>}ID)=0, above(total column(1)),count({<Status={'Open'},Weekend>}ID)),Weekend))*avg(1)

[smilingjohn]

Hi Kush,

The result works perfectly with your suggested expression which is this 

sum(aggr(if(count({<Weekend>}ID)=0,above(total column(1)),count({<Weekend>}ID)),Weekend))*avg(1)

But when I ad my filter condition within the expression it dont work properly . Can u please correct me  the below expression .

sum(aggr(if(count({<Status={'Open'},Weekend>}ID)=0, above(total column(1)),count({<Status={'Open'},Weekend>}ID)),Weekend))*avg(1)

And also when i make some list box selection the result dont act accoding to selections . 

Without making any selection it displays proper result as hown in the below screenshot 

And when i seelct 'aa' from the Dept list box it displays the count only to that perticular dates , rather it should behave like above . 

Thanks 

[Kushal_Chawda]

try below expression. If does not work then share your sample app 

=sum(aggr(sum({<Date>}aggr({<Date>} if(count({<Date>}ID)>0,count({<Status={'Open'},Date>}ID),
above(total column(1))),(Date, (NUMERIC, ASCENDING)))),Date))* avg(1)

[smilingjohn]

HI Kush,

The last expression you suggested is not working , Please find the attachment of the application. 

Thanks

[Kushal_Chawda · Accepted solution]

Without selection of Dept expression works fine, but if you also want to work that for other dimensions then you need to fill the Dates in your Date for each dimension

You may need to update the highlighted code if you want to add more dimensions other than Dept

Data:
LOAD
ID,
Status,
"Date",
AutoNumberHash128("Date"&Dept) as Key,
Dept
FROM [lib://Data/trend.xlsx]
(ooxml, embedded labels, table is Sheet1);

Calendar:
LOAD

Date(MinDate + IterNo() -1,'DD/MM/YYYY') as Date,
WeekEnd(date(MinDate + IterNo() -1,'DD/MM/YYYY')) as Weekend,
Date(MinDate + IterNo() -1,'YYYY') as Year,
Date(MinDate + IterNo() -1,'MM/YY') as Month,
Date(MinDate + IterNo() -1,'YY') & '_' & Num(Week(MinDate + IterNo() -1),'00') as Week

While
MinDate + IterNo() -1 <=MaxDate;

LOAD
min(Date) as MinDate,
Floor(Today()) as MaxDate
//max(Date) as MaxDate

Resident Data;

Dates_Dept_T1:
Load Date
Resident Calendar;

Left Join(Dates_Dept_T1) // Add here other dimensions for which you want it to work
Load Dept
Resident Data;

Dates_Dept_T2:
Load *, AutoNumberHash128("Date"&Dept) as Key2
Resident Dates_Dept_T1;

Drop Table Dates_Dept_T1;

Concatenate(Data)
Load *
Resident Dates_Dept_T2
where not Exists(Key,Key2);

Drop Table Dates_Dept_T2;

Below expression then just works for me with or without selection of Dept

sum({<Weekend>}aggr({<Weekend>} if(count({<Weekend>}ID)>0,count({<Status={'Open'},Weekend>}ID),
above(total column(1))),(Weekend, (NUMERIC, ASCENDING))))* avg(1)