Solved: Re: Weekend counts - Qlik Community

smilingjohn · ‎2020-07-16

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 · ‎2020-07-17

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)

View solution in original post

smilingjohn · ‎2020-07-16

Hi All

Any help please

smilingjohn · ‎2020-07-16

Hi Community

Any suggestions .

Thanks in Advance

Kushal_Chawda · ‎2020-07-16

try below. Column(1) means your first expression. You can change it accordingly

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

smilingjohn · ‎2020-07-16

Thanks for the reply Khush,

Now i see it giving the desired output , What do u mean by above(total column(1)), ?

As i copied your expression and pasted in mine and it worked .

But it is not showing the weekend date till today . The last daet is till March-7-2020 but i need to show it till Tdays date .How can I achieve this ?

Kushal_Chawda · ‎2020-07-16

Column(1) refers to the expression number of your chart. Let's say you have straight table and have two expressions. Then column(1) refers to first expression and column(2) refers to second expression.

If you want to create Dates till today, Can't you create it using master calendar?

smilingjohn · ‎2020-07-16

Thanks Khush

I have the master calendar created from date field .

But it is not giving me the data till date . How can I handle this .

The actual date in the data is till 7-march-2020

And I see the master calendar generating all the missing dates till 7march .

But I want it till today .how do I achieve this .

Thanks

Kushal_Chawda · ‎2020-07-16

can you share calendar script?

smilingjohn · ‎2020-07-16

Hi Khush,

Below is the script

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,
max(Date) as MaxDate

Resident Trend;

smilingjohn · ‎2020-07-16

Hi Khush,

This is the date i have Min date is 12-Oct-2019 and Max date is 4-Mar-2020 .The caledar is generating the dates from 12-Oct-2019 till 4-Mar-2020 but i want the dates to be till today. Thanks

Date

12-Oct-19

24-Nov-19

2-Dec-19

12-Jan-20

4-Mar-20