Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

SQL Running

Hello All

I need your help urgently in the below query. I am missing something important. Please guide me

Below is the query where I am using the cumulative one. But when I am using it in visualization, it again sum up via individual dates.

I want to calculate the cumulative via SQL Query and take that one in visualization directly.

LIB CONNECT TO 'TIGUNVMBGWL0011';

ACCOUNT:

LOAD

     PGLD,

     PMONTH,

    CLN,

    DIN,

    MAN,

    CUMULATIVE_SALES,

    ALID;

SQL SELECT

  CONVERT(VARCHAR(7),PGLD,126) AS PMONTH,

    CLN,

    DIN,

    MAN,

    SUM(ALID) OVER (ORDER BY PGLD ROWS UNBOUNDED PRECEDING) AS CUMULATIVE_ALID,

    ALID

FROM tempdb.dbo."AT"

GROUP BY

    CONVERT(VARCHAR(7),PGLD,126),

    PGLD,

    CLN,

    DIN,

    MAN,

    ALID;

Cumulative.png

Kindly help me. Below attached screenshot of visualization and data file

stalwar1

gwassenaar

Thanks

Deepanshu

2 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Seepanshu,

in your SQL statement, the CONVERT command with the parameter 126 converts your data to a full timestamp.

In order to create a Monthly chart in Qlik Sense, you can create a Calendar with all necessary calendar fields - Year, Month, Day, etc... There is plenty of information in the Community about the process of creating a calendar. Then, use the field MonthYear as a Chart Dimension.

Alternatively, if you load your data using the Data Manager and tag your date field as a Date, Qlik Sense will create an automated "calendar" using Derived Fields. You can use one of these fields for your chart.

This is quite a basic question. Perhaps you should consider taking a Qlik Sense class and cover the basics. If you can't take a class, then maybe you could learn the basics from one of the several Qlik books available in the market. For example, you can learn the basics and the advanced techniques for both QlikView and Qlik Sense from my book QlikView Your Business.

cheers,

Oleg Troyansky

Anonymous
Not applicable
Author

Thanks for your reply Sir..

I already go through the basics of Qlik Sense. I know how to use the Master Calendar.

I try to use the master calendar but it fails... If you look the query I used Varchar(7) which means I restricting the data via month but still it does not work.

Might be this is the basic question for you not for everyone.

What I want is to calculate the running total in SQL query not in the visualization. The reason is that when I am using the running total in visualization I can not filter it on month or year or quarter basis.

Sir if for you the above question is basic.. Can you take 5-10 minutes and attach the qvf or qvd file where the above issue resolve. It would be grateful for me..

Thanks